topic Re: Macro to run a project through a list of date ranges and output the value for each date range in SAS Enterprise Guide

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

zhang_l — Thu, 23 Jun 2016 03:15:18 GMT

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

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

RahulG — Thu, 23 Jun 2016 03:35:42 GMT

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.

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

Kurt_Bremser — Thu, 23 Jun 2016 05:31:17 GMT

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.

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

RahulG — Thu, 23 Jun 2016 05:56:16 GMT

Valuable information

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

RW9 — Thu, 23 Jun 2016 08:23:41 GMT

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;