Macro to run a project through a list of date ranges and output the value for each date range

[zhang_l]

I have build a SAS EG project which counts the total number of people (denoted Mbr_No) that have done a transaction in between a 3 month period. Currently I manually change this 3 month date range and then copy out the result. Is there a way to write a macro that can run through a list of date ranges and output the final count of people who have done a transaction.

Data is in the form of

Mbr_No                    TransactionDate                 TransactionAmount                  TransactionCode

900XX1                     01MAR2016                                              $2.5                                   300

900XX1                     01MAR2016                                              $40                                    300

900XX1                     03MAR2016                                              $5                                      301

800XX3                     03MAY2016                                             $40                                     205

I need to run the same project over the last 3 years so the date range would be

Start Date                           End Date

01JAN2013                         31MAR2013

01FEB2013                         30APR2013

01MAR2013                        31May2013

          .                                         .

          .                                         .

          .                                         .

01JAN2016                         31MAR2016

01FEB2016                         30APR2016

01MAR2016                        31May2016

Using SAS EG 7.1

Li

[RahulG]

1. Create the macro with two parameter start date and end date 

%count_tot_no_peple(start_dt, end_dt);

2. In another dataset DATE_RANGE, store your START_DATE and END_DATE value in two columns.

Below step would call the count_tot_no_people for all rows in DATE_RANGE table.

data _null_;

set DATE_RANGE;

CALL EXECUTE("%count_tot_no_peple(" || START_DATE "," || END_DATE ");" );

run;

Note- Do refer to CALL EXECUTE to get right syntax.

[Kurt_Bremser]

When calling a macro with call execute, use single quotes to prevent macro execution/resolution at data step compile time. Single quotes make sure that the macro is only executed/resolved when the data step is finished and the code that has been pushed into the interpreter queue is then executed.

[RahulG]

Valuable information

[RW9]

Not sure why you would need a macro for this?

proc sql;
  create table WANT as
  select  A.DATE as START_DATE,
          intnx('month',A.DATE,3) as END_DATE,
          (select count(distinct MBR_NO) from HAVE where DATE between A.DATE and intnx('month',A.DATE,3)) as MBR_NO_COUNT
  from    HAVE;
quit;

Not tested as no test data (in the form of a datastep), but that should work.  Might take a while running.  Alternatively you could do the same in datastep via various methods, hash table comes to mind, merging etc.  You could also generate the code:

data _null_;
  input start_date end_date;
  call execute('proc sql; create table WANT'||strip(_n_)||' as select count(distinct MBR_NO) from HAVE where DATE between "'||put(start_date,date9.)||'"d and "'||put(end_date,date9.)||'"d; quit;');
  format start_date end_date date9.;
datalines;
01jan2015 01mar2015
;
run;