06-22-2016 11:15 PM
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
Using SAS EG 7.1
06-22-2016 11:35 PM
1. Create the macro with two parameter start date and end date
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.
CALL EXECUTE("%count_tot_no_peple(" || START_DATE "," || END_DATE ");" );
Note- Do refer to CALL EXECUTE to get right syntax.
06-23-2016 01:31 AM
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.
06-23-2016 04:23 AM
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;