Need help expanding a looping macro to include dates

[SASGeek]

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

[PaigeMiller]

Step 1 is always to create working SAS code for one specific set of dates, without using macros and without using macro variables.

Once you have that code, creating a working macro is much easier. Show us that code.

By the way

%LET datex = 202311 202312 202403;

in my opinion, it is a mistake to work with years and months which are integers like 202311, which SAS does not understand as dates even though they look like dates to humans, unless you convert them to valid SAS dates in the code.

[Patrick]

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
;

[Tom]

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;