topic Re: Is there a way to reference date macro variable in proc SQL like operator? in SAS Programming

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

SASlearner60 — Sun, 19 Jan 2025 07:48:02 GMT

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;

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

FreelanceReinh — Sun, 19 Jan 2025 09:22:00 GMT

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;

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

SASlearner60 — Sun, 19 Jan 2025 10:08:56 GMT

Hello @FreelanceReinh

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

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

PaigeMiller — Mon, 20 Jan 2025 10:24:20 GMT

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

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

yabwon — Mon, 20 Jan 2025 10:43:36 GMT

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;