BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
SASlearner60
Calcite | Level 5

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;

1 ACCEPTED SOLUTION

Accepted Solutions
FreelanceReinh
Jade | Level 19

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;

 

View solution in original post

4 REPLIES 4
FreelanceReinh
Jade | Level 19

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;

 

SASlearner60
Calcite | Level 5

Hello @FreelanceReinh 

 

Thank you for your swift response! This works like a charm!

yabwon
Onyx | Level 15

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;

 

_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation



PaigeMiller
Diamond | Level 26

@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.

--
Paige Miller

sas-innovate-wordmark-2025-midnight.png

Register Today!

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.


Register now!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 424 views
  • 6 likes
  • 4 in conversation