BookmarkSubscribeRSS Feed
Calcite | Level 5

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



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;
%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
) ;
%mend books


Super User Tom
Super User

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.),"'"));
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;

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;

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.


Opal | Level 21

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:



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

Meteorite | Level 14

There seems to be several problems with your code:

  1. As @Tom already wrote, you must get the Oracle date strings into single quotes
  2. 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.
  3. 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

filename tempsas temp;
data _null_;
  set months;
  file tempsas;
  '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 /
  ' ) ; '/

%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. 

Register now!

How to Concatenate Values

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.

Get the $99 certification deal.jpg



Back in the Classroom!

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

View all other training opportunities.

Discussion stats
  • 3 replies
  • 4 in conversation