Is there a way to reference date macro variable in proc SQL like operator?

[SASlearner60]

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;

[FreelanceReinh · Accepted solution]

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]

Hello @FreelanceReinh 

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

[yabwon]

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;

[PaigeMiller]

---

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