Help using Base SAS procedures

to_date

Accepted Solution Solved
Reply
Contributor
Posts: 67
Accepted Solution

to_date

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


Accepted Solutions
Solution
‎03-21-2015 03:26 AM
Respected Advisor
Posts: 3,892

Re: to_date

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

View solution in original post


All Replies
Super User
Posts: 9,681

Re: to_date

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

Solution
‎03-21-2015 03:26 AM
Respected Advisor
Posts: 3,892

Re: to_date

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

Contributor
Posts: 67

Re: to_date

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

Occasional Contributor
Posts: 6

Re: to_date

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.

Contributor
Posts: 67

Re: to_date

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

Contributor
Posts: 67

Re: to_date

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

Respected Advisor
Posts: 3,892

Re: to_date

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?

☑ This topic is SOLVED.

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

Discussion stats
  • 7 replies
  • 5475 views
  • 8 likes
  • 4 in conversation