SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

proc sql to oracle date with date macro variable

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 98
Accepted Solution

proc sql to oracle date with date macro variable

[ Edited ]

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


Accepted Solutions
Solution
‎07-12-2017 08:18 AM
Super User
Posts: 3,250

Re: proc sql to oracle date with date macro variable

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


All Replies
Super User
Posts: 19,770

Re: proc sql to oracle date with date macro variable

Macro variables don't resolve inside single quotes.

 

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

Super User
Posts: 5,497

Re: proc sql to oracle date with date macro variable

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;

Solution
‎07-12-2017 08:18 AM
Super User
Posts: 3,250

Re: proc sql to oracle date with date macro variable

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 ........
Super User
Posts: 5,497

Re: proc sql to oracle date with date macro variable

You may need to go a step further:

 

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

 

 

Frequent Contributor
Posts: 98

Re: proc sql to oracle date with date macro variable

[ Edited ]
Posted in reply to Astounding

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

Frequent Contributor
Posts: 98

Re: proc sql to oracle date with date macro variable

Thanks a lot, Kiwi! The code works!
☑ This topic is solved.

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

Discussion stats
  • 6 replies
  • 232 views
  • 2 likes
  • 4 in conversation