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();
... View more