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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.