topic Re: to_date in SAS Procedures

to_date

Belle — Sat, 21 Mar 2015 03:30:44 GMT

Hello,

I am trying to automate program that pull data from Oracle in certain period of time, but I keep getting error. Could you please help me to see how to fix this. Thank you.

%macro setup;

data temp;

TODAY = TODAY();

nextday = INTNX('MONTH',TODAY,3);

CURQ = QTR(today);

format today nextday date9.;

if CURQ = 1 then

do;

/**** reporting current year ***/

adm_cur_frm = put(intnx('year',today, -1), mmddyy10.);

adm_cur_to = put(intnx('year',today, -1,'end'), mmddyy10.);

end;

else

do;

/**** reporting current year ***/

adm_cur_frm = put(intnx('year',today, 0), mmddyy10.);

adm_cur_to = put(intnx('Qtr',nextday, 0,'end'), mmddyy10.);

end;

CALL SYMPUTX('adm_cur_frm',adm_cur_frm);

CALL SYMPUTX('adm_cur_to',adm_cur_to);

RUN;

%put &adm_cur_frm;

%put &adm_cur_to;

%mend;

%setup;

libname lib1 oracle schema='&schema' defer=yes path=&path user=&user pw="&pw";

%macro get(period=);

proc sql;

create table test as

select *

from lib1.chrg_itm

where adm_dt between to_date("&&adm_&period._frm.","mm/dd/yyyy")

and to_date("&&adm_&period._to.","mm/dd/yyyy")

;

quit;

%mend;

%get(period=cur);

ERROR: Function TO_DATE could not be located.

ERROR: Expression using BETWEEN has components that are of different data types.

The data type of 'adm_dt' is below.

Type Len Format Informat

Num 8 DATETIME20. DATETIME20.

I also tried the following, but the program just keep running, I have to force SAS to kill the program.

where adm_dt between &&adm_&period._frm and &&adm_&period._to

Another one I tried below, I got zero record back, but I know there are records in between this period, can't be zero.

where pstng_dt between input(strip("&&adm_&period._frm."),mmddyy10.)

and input(strip("&&adm_&period._to."),mmddyy10.)

Any suggestion will be helpful.

Thanks

Re: to_date

Ksharp — Sat, 21 Mar 2015 06:35:18 GMT

You should use Pass Through SQL , not SAS SQL , SAS don't have such function to_date()

Re: to_date

Patrick — Sat, 21 Mar 2015 07:26:34 GMT

As states you can't use an Oracle function in an implicit SQL.

With implicit SQL the SAS/Access to Oracle engine will try an push as much of the SQL to Oracle by translating the SAS SQL syntax into Oracle SQL syntax and then send this Oracle syntax to the data base for execution. You can also code such Oracle syntax directly using pass-through SQL which then allows you to take full advantage of all the functions Oracle has on offer.

When using implicit SQL make sure you define options "options sastrace=",,,d" sastraceloc=saslog nostsuffix;" as this will show you in the log what SQL SAS was able to send to Oracle for execution. Only use functions which can be pushed to Oracle SAS/ACCESS(R) 9.4 for Relational Databases: Reference, Sixth Edition else use explict SQL.

For your sample implicit SQL can work. Try below code (note that I'm using "date9." as format for the date strings).

options sastrace=",,,d" sastraceloc=saslog nostsuffix;

data _null_;

adm_cur_frm = put(intnx('year',TODAY(), 0,'b'), date9.);

adm_cur_to = put(intnx('year',TODAY(), 0,'e'), date9.);

CALL SYMPUTX('adm_cur_frm',adm_cur_frm);

CALL SYMPUTX('adm_cur_to',adm_cur_to);

stop;

RUN;

%put &adm_cur_frm;

%put &adm_cur_to;

libname lib1 oracle schema='&schema' defer=yes path=&path user=&user pw="&pw";

%macro get(period=);

proc sql;

create table test as

select *

from lib1.chrg_itm

where adm_dt between "&adm_cur_frm"d and "&adm_cur_to"d

;

quit;

%mend;

%get(period=cur);

Re: to_date

Belle — Sat, 21 Mar 2015 15:14:42 GMT

Thanks very much Xia Keshan and Patrick. Specially, Patrick's code works great.

Re: to_date

JayCorbett — Sun, 22 Mar 2015 02:35:09 GMT

I realize that the core problem has been solved, however (in the original code) since the macro vars were created in one macro but used in another, they need to be %GLOBAL.

Re: to_date

Belle — Thu, 26 Mar 2015 03:50:23 GMT

Hi Jay, yes, I did set to %GLOBAL in my program. Thanks

Re: to_date

Belle — Thu, 26 Mar 2015 03:55:03 GMT

Hi, I encounter another problem. I tried to run the same code on the mainframe, but I got another error saying

ERROR: ORACLE prepare error: ORA-00905: missing keyword.

When I didn't include where statement (where adm_dt between "&adm_cur_frm"d and "&adm_cur_to"d), it worked fine; Why the same code doesn't work on Mainframe (JCL).

PROC SQL;

CONNECT TO ORACLE

(USER=&user PASSWORD="&PW" PATH=&path);

CREATE TABLE TEST AS

SELECT * FROM CONNECTION TO ORACLE

(select *

from lib1.chrg_itm

where adm_dt between "&adm_cur_frm"d and "&adm_cur_to"d

);

DISCONNECT FROM ORACLE;

quit;

%mend;

%get(period=cur);

Please help.

Thanks

Re: to_date

Patrick — Thu, 26 Mar 2015 07:05:23 GMT

Can you please start a new discussion / post the new problem as a new question?

Not sure why this code shouldn't work under z/OS. It's still SAS code.

Can you run the following on the Mainframe?

data test;

format dttm date9.;

dttm="&adm_cur_frm"d;

output;

stop;

run;

Are you getting any warnings in your log - eg. that the macro variables are not defined?