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