First let's clean up your first program. Define the macro BEFORE the code that needs to use it, not in the MIDDLE of the code that is going to use it. It will make it much, much easier to read and understand and keep straight what is the macro and what is the actual SAS program. Second do not use "magic" macro variables in you macro definitions. That is references to macro variables that are neither inputs to the macro nor defined as LOCAL (or perhaps GLOBAL) by the macro. How is the reader of the macro definition supposed to know what these are and where they get their values? They must get them by magic!
Your macro just needs one input, the list of date strings. It can count how many there are if it needs to know.
%MACRO CreateTable(dates);
%local pers i date ;
%let pers=%sysfunc(countw(&dates,%str( )));
%DO i=1 %TO &pers.;
%LET DATE = %SCAN(&dates,&i,%str( ));
CREATE TABLE fruit_&date. as
select * from connection to odbc
( select a.apple_count
, a.pears_count
, a.blueberry_count
from cary.stores.fruit_inventory a
where a.as_of_date = &date.
)
;
%end;
%mend;
Now you can use that macro in your program code. Note you cannot disconnect from ODBC after you have ended PROC SQL, that will cause an error. And if you are ending PROC SQL it will disconnect for you.
%LET datex = 202311 202312 202403;
PROC SQL;
connect to odbc (&odbcstring.);
%CreateTable(&datex);
quit;
So now let's look at your revised macro. Looks like you are saying you want to start with strings like 2023-11-04 and end up with strings like '2023-11-04' and '2023-11-30' and '202311' and 202311. So let's make each of those a different macro variable, QYYMMDD, QYYMMEND, QYYYYMM and YYYYMM. To make it easier let's also make a macro variable that has an actual SAS date values, let's call that DATE.
%MACRO CreateTable1(dates);
%local pers i date qyymmdd yyyymm qyyyymm;
%let pers=%sysfunc(countw(&dates,%str( )));
%do i=1 %TO &pers.;
%LET DATE = %sysfunc(inputn(%scan(&dates,&i,%str( )),yymmdd10.));
%let qyymmdd=%sysfunc(quote(%sysfunc(putn(&date,yymmdd10.)),%str(%')));
%let qyymmend=%sysfunc(quote(%sysfunc(intnx(month,&date,0,e),yymmdd10.),%str(%')));
%let yyyymm=%sysfunc(putn(&date,yymmn6.));
%let qyyyymm=%sysfunc(quote(&yyyymm,%str(%')));
CREATE TABLE varlist_&yyyymm. as
select * from connection to odbc
(select &qyyyymm as period
, a.apple_count
, b.pears_count
, c.cherry_count
from bigshop.stores.red_inventory a
left outer join bigshop.stores.yellow_inventory b
on a.source_id = b.source_id
left outer join bigshop.stores.berry_inventory c
on 1=1 /* You left out the ON condition for this second left join */
where a.as_of_date = &qyymmdd
and b.picking_date = &qyymmend
and c.fresh_date = &yyyymm
)
;
%end;
%mend;
Note that you did not include any ON condition for the second LEFT JOIN so I just coded it as 1=1 which is alwyas TRUE. I also left the spurious OUTER keywords because perhaps the passthru databases implementation of SQL requires that syntax.
Now you can call it the same way, only making sure to pass in string that the YYMMDD informat can understand.
%LET datex = 2023-11-04 2023-12-15 ;
PROC SQL;
connect to odbc (&odbcstring.);
%CreateTable(&datex);
quit;
... View more
