Hello all,
I have use the following macro quite frequently; however, I need to make modifications and I'm totally lost. Can someone help please? The sample below has only 3 periods, but I work with economists so I need to go back between 10 - 25 periods (variable depending on need) so a macro is really the only way to go.
original simple macro
%LET pers = 3;
%LET datex = 202311 202312 202403;
PROC SQL;
connect to odbc (&odbcstring.);
%MACRO CreateTable;
%DO i=1 %TO &pers.;
%LET DATE = %SCAN(&datex,&i);
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;
%CreateTable;
quit; disconnect from odbc;
Adjusted macro (is there a better way to go about this????)
Now I need to use a mix of real dates and year_month dates. I need the following adjustments
&dt - must be in yr_month number so I can make it a column in my SQL table so it probably needs quotes
&date1 - must be in yr_month_date order, with quotes so I can pull it from the SQL table
&date2 - must be in the month-end-date from whatever date is from &date1
&date3 - must be a numeric version of &dt.
Here's my attempt at starting this but of course it's not working.
%LET pers =3;
%LET datex = 2023-11-25
2023-12-29
2024-03-29;
PROC SQL;
connect to odbc (&odbcstring.);
%MACRO CreateTable1;
%DO i=1 %TO &pers.;
%LET DATE = %SCAN(&datex,&i);
CREATE TABLE varlist_&dt. as /**(where &dt needs to be in the YYYYMM form ie 202311) **/
select * from connection to odbc (
select '&dt.' as period /** where &dt is the value 202311, either numeric or character **/
, 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
where a.as_of_date = &date1. /** this has to be in '2023-11-25' with the quotes **/
and b.picking_date = &date2 /** this now needs to be month end for the month, namely, '2023-11-30' with the quotes **/
and c.fresh_date = &date3 /** this has to be in year_month date but as numeric, 202311 **/
);
%end;
%mend;
%CreateTable1;
quit;
disconnect from ODBC;
I'm beyond lost. Thanks for any help you're willing to provide.
Paula
... View more
