BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Belle
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

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

7 REPLIES 7
Ksharp
Super User

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

Patrick
Opal | Level 21

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
Obsidian | Level 7

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

JayCorbett
Calcite | Level 5

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
Obsidian | Level 7

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

Belle
Obsidian | Level 7

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
Opal | Level 21

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?

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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