BookmarkSubscribeRSS Feed
zhang_l
Calcite | Level 5

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

  

4 REPLIES 4
RahulG
Barite | Level 11

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
Super User

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 1227 views
  • 2 likes
  • 4 in conversation