topic passing dates to filter on oracle for macro program in SAS Programming

passing dates to filter on oracle for macro program

KatWinSASland — Mon, 03 Jul 2023 01:07:48 GMT

I am trying to create a macro program that iterates over a dataset in Oracle and filters on the publish_yr_mth variable and publish date. So I would have a publish date of something like 201001 (Jan 2010) and then publish_dt>01-JAN-2010. And then move to the next set of dates (but not always at even intervals, just whatever is set in the dataset).

When I create the macro, it will work with the publish_yr_mnth, but I cannot even just put in a string for the publish_dt or the macro won't run. How do I pass a date that's readable to Oracle in this macro . Outside of macros, the 'DD-MON-YYYY' is being accepted as the correct format and will bring back a result.

Or is there something else causing this not to run?

non-marco version that will run:

proc sql ;

connect to oracle as mydb(user=user orapw="&dbpass" path=oracledb );

create table bk_samp as select * from connection to mdb (

     select author ,title from books_db where publish_dt >=’01-JAN-2010’ and publish_yr_mnth=201001  ) ;

quit;

Macro version that I'm trying to run:


data months;
input publishdt publish_yr_mnth;
datalines ;
201001 01-JAN-2010
201002 02-FEB-2010
201005 02-MAY-2010
;

proc sql ;
select distinct publishdt
into: publishdt1-
from months;
quit
p

%macro books(publishdt, publish_yr_mnth): 
%do i =1 %to &sqlobs; proc sql ; 
connect to oracle as mydb(user=user orapw="&dbpass" path=oracledb );
 create table bk_samp as select * from connection to mdb (
      select author ,title from books_db where publish_dt >=&&publishdt&i and publish_yr_mnth=&&publishyrmth&i
 ) ;
 quit; 
%end; 
%mend books
%books();

Re: passing dates to filter on oracle for macro program

Tom — Mon, 03 Jul 2023 03:27:02 GMT

If ORACLE accepts 'MM-MON-YYYY' strings as valid date literals then that is what you should put into the macro variable.

data _null_;
  call symputx('oracle_date',quote(put(date(),date11.),"'"));
run;

1    data _null_;
2      call symputx('oracle_date',quote(put(date(),date11.),"'"));
3    run;

NOTE: DATA statement used (Total process time):
      real time           0.06 seconds
      cpu time            0.01 seconds


4    %put &=oracle_date;
ORACLE_DATE='02-JUL-2023'

If you must do it in pure macro code (why???) then try something like:

%let oracle_date=%bquote('%sysfunc(date(),date11.)');
%put &=oracle_date;

5    %let oracle_date=%bquote('%sysfunc(date(),date11.)');
6    %put &=oracle_date;
ORACLE_DATE='02-JUL-2023'

If the macro quoting causes you trouble wrap the whole thing in an %unquote() call.

%let oracle_date=%unquote(%bquote('%sysfunc(date(),date11.)'));

PS Your example macro has flawed logic. It is just creating the same dataset over and over again.

Re: passing dates to filter on oracle for macro program

Patrick — Mon, 03 Jul 2023 11:06:11 GMT

I believe Oracle will accept a string like '01-JAN-2010' in a where clause and interpret it as a date if used for comparison with a variable of type date. If not then use Oracle function to_date()

Oracle will need the date string in single quotes but SAS will not resolve a macro variable that's embedded in single quotes. To get around this use the following in your Oracle where clause:

%tslit(&<your macro var with the date string>)

Sample code

%let test=01-JAN-2010;
%put %tslit(&test);

Printed to SAS log:

'01-JAN-2010'

If it was me then I'd probably would first try to go for an approach where I generate the where clause condition and then query the database only once. You might be able to generate such code with a SQL Select ... into :where_expression ....

Re: passing dates to filter on oracle for macro program

s_lassen — Mon, 03 Jul 2023 11:00:52 GMT

There seems to be several problems with your code:

As @Tom already wrote, you must get the Oracle date strings into single quotes
You swapped the PUBLISH_DT and the PUBLISH_YR_MNTH variables in the MONTHS table, and your input will not work (at least the second variable should be character) - I assume you have a MONTHS table from elsewhere, and did not test the code to create it as shown.
The code will overwrite the same output table (WORK.BK_SAMP) repeatedly

Instead of using a macro, I would consider writing the program to a temporary SAS file, e.g.

data months;
  input publish_yr_mnth 7. publishdt $11.;
datalines ;
201001 01-JAN-2010
201002 02-FEB-2010
201005 02-MAY-2010
;run;  

filename tempsas temp;
data _null_;
  set months;
  file tempsas;
put 
  'proc sql ; ' /
  '  connect to oracle as mydb(user=user orapw="&dbpass" path=oracledb );' /
  '  create table bk_samp' _N_ ' as select * from connection to mdb (  '/
  '    select author ,title from books_db where publish_dt >=''' publishdt +(-1) '''  and publish_yr_mnth=' publish_yr_mnth /
  ' ) ; '/
  'quit;';  
  ;

%include tempsas / source2;

To test the code, you can start by commenting out the "file tempsas" line and the "%include" line, and just look at the generated code in the log until it's OK, then uncomment and run for real.

I put in the _N_ after "BK_SAMP" so that 3 tables are created (BK_SAMP1-3). You may want to use the PUBLIST_YR_MNTH as a suffix instead, or collect all the data in a single table (remember to select the PUBLIST_YR_MNTH column also in that case).