How to use macro date variable in a query

[lizzy28]

Hi everyone,

This is a similar question to what I posted last time.

I tried plug in a date macro variable (&curdat_2mon as in the code below) in my query. But it looks the quote (%str(%')) I used is not right? Can anyone help me correct again? If there is a simplified way to use the macro variable in the query, that would be great too. Thanks a lot!

data _null_;

curdat_2m = intnx('Month',"&sysdate"d,-2,'s');

call symput ('curdat_2mon',put(curdat_2m,date9.)) ;

run;

%put &curdat_2mon ;

PROC SQL;

CREATE TABLE new_tbl as

select distinct MBR_ID, FST_NM, LST_NM, DOB, ZIP

from DB.MBR_TBL

where  %str(%')&curdat_2mon.%str(%')d

between BEG_DT and END_DT

;

QUIT;

[Reeza · Accepted solution]

Either don't apply the format, the easiest method, or in your usage of the variable just use double quotes.

PROC SQL;
CREATE TABLE new_tbl as
select distinct MBR_ID, FST_NM, LST_NM, DOB, ZIP
from DB.MBR_TBL
where  "&curdat_2mon"d
between BEG_DT and END_DT
;

Easier:

data _null_;
curdat_2m = intnx('Month',"&sysdate"d,-2,'s');
call symput ('curdat_2mon',curdat_2m) ;
run;

%put &curdat_2mon ;

PROC SQL;
CREATE TABLE new_tbl as
select distinct MBR_ID, FST_NM, LST_NM, DOB, ZIP
from DB.MBR_TBL
where  &curdat_2mon.
between BEG_DT and END_DT
;
QUIT;

[Quentin]

I would suggest using double quotes:

where "&curdat_2mon"d
between BEG_DT and END_DT
;

That avoids the need to %STR() for macro quoting.

If you really want to use single quotes, it should work if you %unquote it yourself, e.g.:

where  %unquote(%str(%')&curdat_2mon.%str(%')d)
between BEG_DT and END_DT
;

The macro processor sometimes failes to unquote code automatically.

[lizzy28]

Thanks a lot, Reeza and Quentin!