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:
input publishdt publish_yr_mnth;
proc sql ;
select distinct publishdt
%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
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.
PS Your example macro has flawed logic. It is just creating the same dataset over and over again.
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>)
%let test=01-JAN-2010; %put %tslit(&test);
Printed to SAS log:
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 ....
There seems to be several problems with your code:
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).
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.