sas4gl

How to pass date parameter to query in temporary (work) table?

I wrote a few sas variables regarding dates. I also wrote a chunk of code inc_mails where I query db (using passthrough) and another chunk of code where I use temp table (work) to limit inc_mails to last month. When I pass start_sas_month and end_sas_month parameters to temp and run it then syntax error comes up (Error 22-322: syntax error). I don't know what's wrong.

My code below:

%let n_months = 1;

%let date = %sysfunc(date());

%let start_month = %sysfunc(intnx(month, &date., -&n_months.));
%let start_sas_month = %str(%')%sysfunc(putn(&start_month., date9.))%str(%'); %put &start_sas_month.;

%let end_month =  %sysfunc(intnx(day, %sysfunc(intnx(month, &date., 1-&n_months., b)), -1, s));
%let end_sas_month = %str(%')%sysfunc(putn(&end_month., date9.))%str(%'); %put &end_sas_month.;


proc sql;
connect to oracle as xyz (authdomain="XYZ_READ" path=XYZ_P);
create table work.inc_mails as 
select * from connection to xyz

(   
    SELECT 
    ap.Id,
    ap.Completed_Date

    FROM xy.Appointments ap
); 

disconnect from xyz;
Quit;


proc sql;
CREATE TABLE final_tbl AS
SELECT
i.Id,
i.Completed_Date

FROM work.inc_mails i

WHERE 
i.Completed_Date >= &start_sas_month.
AND i.Completed_Date <= &end_sas_month.
;
quit;

Above is just a short example of my real code.

I expected to get proper result without any error where dataset limited by sas date would be returned.

Solution

  • Most likely there are two issues with your code.

    First you are treating COMPLETED_DATE as a if it contained a character string by doing a comparison like:

    Completed_Date >= '01FEB2024'

    So if COMPLETED_DATE is a character string you won't get any syntax errors, but the comparison will not work properly is '02JAN2024' comes after '01FEB2024' when comparing strings.

    If you wanted to treat COMPLETED_DATE as if is had DATE values you would need to either add a letter D so that you are comparing to a date value.

    Completed_Date >= '01FEB2024'd

    Or just just use the actual integer value SAS uses to store that date (which you stored in a different macro variable).

    Completed_Date >= 23407

    The other issue is that ORACLE generally does not have a DATE variable type. Only DATETIME variable types. So when you extracted the data from your ORACLE database into a SAS dataset the COMPLETED_DATE variable probably became a DATETIME value (number of seconds since 1960) instead of a DATE value (number of days since 1960).

    You could just use the DATEPART() function to extract the number of days from the number of seconds.

    WHERE datepart(i.Completed_Date) between &start_month and &end_month