Old query
Data x ;
set y;
where Offer_St_dt >= '01OCT2011'd;
run;
I am trying to replace the above query with
New Query
%let year1 = %STR(%')%sysfunc(intnx(month,%sysfunc(today()),-12),date9.)%STR(%');
Data x ;
set y;
where Offer_St_dt >= &year1;
run;
I am getting error in the above case as
ERROR: Syntax error while parsing WHERE clause.
ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, a numeric constant, a datetime constant, a missing value, (, +, -, :, INPUT, NOT, PUT, ^, ~.
ERROR 76-322: Syntax error, statement will be ignored.
Change to:
%let year1 =%sysfunc(intnx(month,%sysfunc(today()),-12),date9.);
It is not working. i am getting the same error.
In your macro approach, you forgot the d at the end of the date literal, and you need to manually %unquote() the macro variable (this is an old macro language bug, it should unquote it for you but sometime it doesn't... when code from MPRINT looks right but fails, try %unquote(). Ksharp's non macro approach works for me as well. Code below.
%let year1 = %STR(%')%sysfunc(intnx(month,%sysfunc(today()),-12),date9.)%STR(%')d; data have; Offer_St_dt="01Jan2012"d; output; Offer_St_dt="01Jan2014"d; output; run; %put &year1; data want ; set have; where Offer_St_dt >= %unquote(&year1); run; data want2 ; set have; where Offer_St_dt >=intnx('month',today(),-12) ; run;
Why not write it in data step?
Data x ;
set y;
where Offer_St_dt >=intnx('month',today(),-12) ;
run;
A more easier approach would be setting some global macro variables by a sas-datastep.
When this is a standard approach it could be a standard setting part of sas start.
By that you can verify the content of the macro var. Leave it as text and put the quotes and d indication when needed
Why are you using single quotes instead of double quotes? Macro references do not resolve inside of single quotes.
%let year1 = "%sysfunc(intnx(month,%sysfunc(today()),-12),date9.)"d ;
Data x ;
set y;
where Offer_St_dt >= &year1;
run;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.