Help using Base SAS procedures

How to use macro date variable in a query

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 98
Accepted Solution

How to use macro date variable in a query

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;


Accepted Solutions
Solution
‎07-17-2017 04:12 PM
Super User
Posts: 19,810

Re: How to use macro date variable in a query

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;

 

View solution in original post


All Replies
Solution
‎07-17-2017 04:12 PM
Super User
Posts: 19,810

Re: How to use macro date variable in a query

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;

 

PROC Star
Posts: 1,322

Re: How to use macro date variable in a query

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.

Frequent Contributor
Posts: 98

Re: How to use macro date variable in a query

Thanks a lot, Reeza and Quentin!
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 152 views
  • 2 likes
  • 3 in conversation