what is wrong with my code (calculating dates using intnx)?

[jffeudo86]

hello,

below is a simplified version of my code.  what's wrong with it and how can I correct it?

%macro test(mydate);

%put 1 &mydate.;

proc sql;
select * from xx where datepart(startdate) = &mydate.; *&mydate evaluates to '31AUG2018'd ;
quit;

/* calculate 1 year prior */
%let mydate = %sysfunc(intnx(year,&mydate,-1,same), date9.);
%put 2 &mydate.;

proc sql;
select * from xx where datepart(startdate) = &mydate.; *&mydate evaluates to 31AUG2017 and doesn't work;
quit;

%mend test;

%test('31AUG2018'd);

[Tom · Accepted solution]

In the first case your macro variable's value looks like a date literal to SAS.

'31AUG2018'd

Then your %LET statements make a value that does NOT look like a date literal

31AUG2018

So you either need to change how you USE the value in the second query. 

For example add back the quotes and the D suffix.

datepart(startdate) = "&mydate"d

Or how change how you CREATE the new value. 

You can make a date literal, like you passed in the macro call.

%let mydate = "%sysfunc(intnx(year,&mydate,-1,same), date9.)"d ;

Or just let the macro variable have the raw number of days since 1960 value.

%let mydate = %sysfunc(intnx(year,&mydate,-1,same));

[jffeudo86]

Thank you for the explanation. It's educational for me. I'm not an experienced SAS coder and still finding my way. Some people reply with condescending attitude.

[Astounding]

So this works:

where datepart(startdate) = '31AUG2018'd

Why would you expect this to work:

where datepart(startdate) = 31AUG2017

Doesn't that look different?

The second time around try:

where datepart(startdate) = "&mydate"d