topic Re: what is wrong with my code (calculating dates using intnx)? in New SAS User

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

jffeudo86 — Wed, 18 Sep 2019 21:10:43 GMT

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);

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

Tom — Wed, 18 Sep 2019 21:25:25 GMT

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));

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

Astounding — Wed, 18 Sep 2019 21:28:36 GMT

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

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

jffeudo86 — Thu, 19 Sep 2019 14:17:07 GMT

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.