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
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.
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.
Valuable information
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.