I was wondering if there was a way to reference date macro variable in proc SQL like operator.
For example, I want to reference macro variable PREVDATE in like operator. Please refer code example below:
%LET PREVDATE = %sysfunc(intnx(day,%sysfunc(inputn(&sysdate9,date11.)),-2,end),);
%LET PREVDATE = %sysfunc(putn(&PREVDATE,date11.));
%PUT &PREVDATE;
Proc sql;
Create table XYZ as
Select distinct A, B
From C
Where B like '%&PREVDATE.%';
quit;
Hello @SASlearner60 and welcome to the SAS Support Communities!
As a rule, macro variable references such as &PREVDATE are not resolved if they are enclosed in single quotes. In addition, % and & are macro triggers, but here you only want & to be treated as such. Duplicating the first percent sign helps to avoid this ambiguity:
where B like "%%&PREVDATE.%";
Alternatively, you can use the CONTAINS operator (alias: ? [question mark]) and thus avoid the percent signs:
where B ? "&PREVDATE";
Also note that your definition of macro variable PREVDATE can be simplified by creating a date literal from &SYSDATE9:
%let PREVDATE = %sysfunc(putn("&sysdate9"d-2,date11)); %put &PREVDATE;
Hello @SASlearner60 and welcome to the SAS Support Communities!
As a rule, macro variable references such as &PREVDATE are not resolved if they are enclosed in single quotes. In addition, % and & are macro triggers, but here you only want & to be treated as such. Duplicating the first percent sign helps to avoid this ambiguity:
where B like "%%&PREVDATE.%";
Alternatively, you can use the CONTAINS operator (alias: ? [question mark]) and thus avoid the percent signs:
where B ? "&PREVDATE";
Also note that your definition of macro variable PREVDATE can be simplified by creating a date literal from &SYSDATE9:
%let PREVDATE = %sysfunc(putn("&sysdate9"d-2,date11)); %put &PREVDATE;
Two more options that works, just for fun:
%PUT &PREVDATE; /* 18-JAN-2025 */
data C;
a="1";
b="bal bla 18-JAN-2025 bla bal";
output;
a="2";
b="bal bla 19-JAN-2025 bla bal";
output;
run;
Proc sql;
Create table XYZ1 as
Select distinct A, B
From C
Where B like '%' !! "&PREVDATE." !! '%';
Create table XYZ2 as
Select distinct A, B
From C
Where B like cats('%',"&PREVDATE.",'%');
quit;
@SASlearner60 wrote:
I was wondering if there was a way to reference date macro variable in proc SQL like operator.
For example, I want to reference macro variable PREVDATE in like operator. Please refer code example below:
%LET PREVDATE = %sysfunc(intnx(day,%sysfunc(inputn(&sysdate9,date11.)),-2,end),);
%LET PREVDATE = %sysfunc(putn(&PREVDATE,date11.));
%PUT &PREVDATE;
Proc sql;
Create table XYZ as
Select distinct A, B
From C
Where B like '%&PREVDATE.%';
quit;
In my opinion, the only way this code makes sense is if variable B is a date value stored as a character string. This should be avoided, dates should be stored as numeric SAS variables, in which case all of the macro variable code to create &PREVDATE can be greatly simplified. Make your programming easy ... store dates as valid numeric variables that contain date values.
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
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.
Ready to level-up your skills? Choose your own adventure.