topic Re: proc sql to oracle date with date macro variable in SAS Data Management

proc sql to oracle date with date macro variable

lizzy28 — Wed, 12 Jul 2017 12:20:17 GMT

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

Re: proc sql to oracle date with date macro variable

Reeza — Wed, 12 Jul 2017 02:06:18 GMT

Macro variables don't resolve inside single quotes.

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

Re: proc sql to oracle date with date macro variable

Astounding — Wed, 12 Jul 2017 02:18:43 GMT

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;

Re: proc sql to oracle date with date macro variable

SASKiwi — Wed, 12 Jul 2017 02:31:44 GMT

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

Re: proc sql to oracle date with date macro variable

Astounding — Wed, 12 Jul 2017 06:21:19 GMT

You may need to go a step further:

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

Re: proc sql to oracle date with date macro variable

lizzy28 — Wed, 12 Jul 2017 12:20:53 GMT

Thanks a lot, Kiwi! The code works!

Re: proc sql to oracle date with date macro variable

lizzy28 — Wed, 12 Jul 2017 12:23:57 GMT

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