to_date

[Belle]

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

[Ksharp]

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

[Patrick · Accepted solution]

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);

[Belle]

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

[JayCorbett]

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.

[Belle]

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

[Belle]

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

[Patrick]

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?