oracle to sas date conversion

Accepted Solution Solved
Reply
Contributor
Posts: 44
Accepted Solution

oracle to sas date conversion


Hi team,

i have the oracle date in the format '26Jun2015:0:0:0'

i am writting a sas code to fetch the data from oracle.

below is the code.

data _null_;

     date = intnx('day',today(),0);

     date1 = put(date, date10.);

     call symput('CONDATE2', date1);

   run;

data _null_;

  put "&CONDATE2";

run;

proc sql;

select day_idnt

from rdw.time_day_dm

where cast(day_dt as date) = '&CONDATE2';

quit;

I am getting the below error message :

      where cast(day_dt as date) = '&CONDATE2';

                             __

                             22

                             202

ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, (, ), *, **, +, ',', -, '.', /, <, <=, <>, =, >, >=, ?, AND,

              BETWEEN, CONTAINS, EQ, EQT, GE, GET, GT, GTT, IN, IS, LE, LET, LIKE, LT, LTT, NE, NET, NOT, NOTIN, OR, ^, ^=, |, ||,

              ~, ~=.

Any ideas will be great.

thanks


Accepted Solutions
Solution
‎06-25-2015 09:52 PM
Contributor dkb
Contributor
Posts: 53

Re: oracle to sas date conversion

Proc SQL in SAS does not have a CAST function so far as I know, and macro variables are not resolved inside single quotes.

Have you tried

select day_idnt

from rdw.time_day_dm

where day_dt = "&CONDATE2"d;

or

select day_idnt

from rdw.time_day_dm

where datepart(day_dt) = "&CONDATE2"d;

?

View solution in original post


All Replies
Solution
‎06-25-2015 09:52 PM
Contributor dkb
Contributor
Posts: 53

Re: oracle to sas date conversion

Proc SQL in SAS does not have a CAST function so far as I know, and macro variables are not resolved inside single quotes.

Have you tried

select day_idnt

from rdw.time_day_dm

where day_dt = "&CONDATE2"d;

or

select day_idnt

from rdw.time_day_dm

where datepart(day_dt) = "&CONDATE2"d;

?

Super User
Posts: 17,835

Re: oracle to sas date conversion

CAST is a Oracle SQL function not a SAS SQL function.

Use DATEPART instead to convert the datetime to a date variable.

For the macro variable in your WHERE clause you'll need to use double quotes instead of single quotes since macro variables don't resolve inside single quotes.

☑ This topic is SOLVED.

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

Discussion stats
  • 2 replies
  • 313 views
  • 0 likes
  • 3 in conversation