proc sql to oracle date with date macro variable

[lizzy28]

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

[Reeza]

Macro variables don't resolve inside single quotes.

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

[Astounding]

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 · Accepted solution]

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]

You may need to go a step further:

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

[lizzy28]

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

[lizzy28]

Thanks a lot, Kiwi! The code works!