BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
lizzy28
Quartz | Level 8

Hi everyone,

 

I am trying to plug in a date macro variable (&curdat_2m as in the code below) in my query, but somehow I got no rows. When I used specific date like 20070515, the query ran fine. Can you see what problem with the where clause in the query? Thanks a lot!

 

data _null_;

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

call symput ('curdat_2m',put(curdat_2m,yymmddn8.)) ;

run;

PROC SQL;

CONNECT TO ORACLE( ...);

CREATE TABLE new_tbl as

SELECT * FROM CONNECTION TO ORACLE

(

select distinct MBR_ID, FST_NM, LST_NM, DOB, ZIP

from DB.MBR_TBL

where '&curdat_2m.' /*if using '20070515', the query generates a number of rows*/

between to_char(BEG_DT, 'YYYYMMDD')

and to_char(END_DT, 'YYYYMMDD')

);

DISCONNECT FROM ORACLE;

QUIT;

 

Thanks for the help!

Lizi

1 ACCEPTED SOLUTION

Accepted Solutions
SASKiwi
PROC Star

Here is how you get the macro date to resolve but still use single quotes as I suspect Oracle will object to double quotes:

 

where %str(%')&curdat_2m.%str(%') between ........

View solution in original post

6 REPLIES 6
Reeza
Super User

Macro variables don't resolve inside single quotes.

 

Shouldn't that be a variable name though and therefore not have quotes at all?

Astounding
PROC Star

Once you get the quote issue resolved, it is conceivable that you may need to fix the variable names.  This version would be consistent with trying to use &curdat_2m within the SQL code:

 

data _null_;

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

call symput ('curdat_2m',put(curdat_2,yymmddn8.)) ;

run;

SASKiwi
PROC Star

Here is how you get the macro date to resolve but still use single quotes as I suspect Oracle will object to double quotes:

 

where %str(%')&curdat_2m.%str(%') between ........
Astounding
PROC Star

You may need to go a step further:

 

where %unquote(%str(%')&curdat_2m.%str(%')) between ......

 

 

lizzy28
Quartz | Level 8

Thanks a lot, Astounding! This code solve my problem too!

lizzy28
Quartz | Level 8
Thanks a lot, Kiwi! The code works!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 6 replies
  • 3986 views
  • 3 likes
  • 4 in conversation