SAS Macro by week

Question

I would like to convert the below macro into a loop so I can enter the start and end date in order to create weekly tables for that date range. Currently I have to run the macro multiple times by entering the start and end dates individually for each week.

%macro tb(start_, end_);

PROC SQL;
CREATE TABLE tbl_&start_. AS
select a.id,
  &start_. as wk,
  case when a.id=b.id then 1 else 0 end as flg

from tb1 a left join (select  id
                      from tb1
                     where cdate <= &start_.
                     ) b
           on a.id=b.id
where orderdate <= &end_.

;

QUIT;

%mend ;


%tb(03JAN2021, 09JAN2021) ;
%tb(10JAN2021, 16JAN2021) ;
%tb(17JAN2021, 23JAN2021) ;

Answer 1

The INTCK and INTNX functions could be helpful for this task. The following generates a dataset with start and end dates for all weeks in the specified time interval.

data weeks;
  /* Specify start and end. */
  start_date = "01FEB2022"d;
  end_date = "28FEB2022"d;
  /* Calculate number of weeks. */
  num_weeks = intck("week", start_date, end_date);
  /* Iterate over weeks. */
  do i = 0 to num_weeks - 1;
    /* Get the sunday in the week i. */
    start_week = intnx("week", start_date, i);
    /* Add six days = end of week. */
    end_week = intnx("days", start_week, 6);
    /* If you need, convert into character. */
    start_ = put(start_week, date.);
    end_ = put(end_week, date.);
    output;
  end;
run;

If you want weeks starting at mondays you should use "week.2" instead of "week". You could also do e.g. bi-weekly reports... Just check the documentation for Date and Time intervals.

Now you can run your macro via call execute.

data _null_;
  set weeks;
  call execute('%nrstr(%do_for_each_week('||start_||', '||end_||'))');
run;