I would avoid to populate macro variables with quoted strings. Add the quotes when you use the macro variables.
I suggest you first make your SQL work for a single month before you wrap macro logic around it.
%let base_date=2023-11-25;
%let dt=202311;
%let dt2=2023-11-31;
proc sql;
create table varlist_&dt. as /**(where &dt needs to be in the YYYYMM form ie 202311) **/
select
"&dt." as period /* create on the SAS side to reduce data volume sent from the DB */
,*
from connection to odbc (
select
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
a.as_of_date = %tslit(&base_date) /** this has to be in '2023-11-25' with the quotes **/
and b.picking_date = %tslit(&dt2) /** this now needs to be month end for the month, namely, '2023-11-30' with the quotes **/
and c.fresh_date = &dt /** this has to be in year_month date but as numeric, 202311 **/
);
quit;
Once above SQL is returning what you're after you could wrap a macro around it as below
%macro demo(as_of_dates);
%let n_dates=%sysfunc(countw(&as_of_dates,%str( )));
%do i=1 %to &n_dates;
%let base_date=%scan(&as_of_dates,&i,,%str( ));
%put &=base_date;
%let sas_dt_val=%sysfunc(inputn(&base_date,yymmdd10.));
%let dt=%sysfunc(putn(&sas_dt_val,yymmn6.));
%put &=dt;
%let dt2=%sysfunc(intnx(month,&sas_dt_val,0,e),yymmdd10.);
%put &=dt2;
%put;
proc sql;
create table varlist_&dt. as /**(where &dt needs to be in the YYYYMM form ie 202311) **/
select
"&dt." as period /* create on the SAS side to reduce data volume sent from the DB */
,*
from connection to odbc (
select
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
a.as_of_date = %tslit(&base_date) /** this has to be in '2023-11-25' with the quotes **/
and b.picking_date = %tslit(&dt2) /** this now needs to be month end for the month, namely, '2023-11-30' with the quotes **/
and c.fresh_date = &dt /** this has to be in year_month date but as numeric, 202311 **/
);
quit;
%end;
%mend;
%demo(2023-11-25 2023-12-29 2024-03-29)
And here another option how you could execute your SQL for multiple months
%macro demo2(as_of_date);
data _null_;
as_of_date=input(&as_of_date,yymmdd10.);
call symputx('base_date',put(as_of_date,yymmdd10.),'l');
call symputx('dt',put(as_of_date,yymmn6.),'l');
call symputx('dt2',put(intnx('month',as_of_date,0,'e'),yymmdd10.),'l');
run;
proc sql;
create table varlist_&dt. as /**(where &dt needs to be in the YYYYMM form ie 202311) **/
select
"&dt." as period /* create on the SAS side to reduce data volume sent from the DB */
,*
from connection to odbc (
select
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
a.as_of_date = %tslit(&base_date) /** this has to be in '2023-11-25' with the quotes **/
and b.picking_date = %tslit(&dt2) /** this now needs to be month end for the month, namely, '2023-11-30' with the quotes **/
and c.fresh_date = &dt /** this has to be in year_month date but as numeric, 202311 **/
);
quit;
%mend;
data _null_;
input as_of_date:$10.;
cmd=cats('%demo2(',as_of_date,');');
call execute( cmd );
datalines;
2023-11-25
2023-12-29
2024-03-29
;
... View more
