SAS Macro help to loop monthly sas datasets

Question

I have monthly datasets in SAS Library for customers from Jan 2013 onwards with datasets name as CUST_JAN2013,CUST_FEB2013........CUST_OCT2017. These customers datasets have huge records of 2 million members for each month.This monthly datset has two columns (customer number and customer monthly expenses).

I have one input dataset Cust_Expense with customer number and month as columns. This Cust_Expense table has only 250,000 members and want to pull expense data for each member from SPECIFIC monthly SAS dataset by joining customer number.

Cust_Expense
------------
Customer_Number  Month
111              FEB2014
987              APR2017
784              FEB2014
768              APR2017
.....
145              AUG2017
345              AUG2014

I have tried using call execute, but it tries to loop thru each 250,000 records of input dataset (Cust_Expense) and join with corresponding monthly SAS customer tables which takes too much of time. Is there a way to read input tables (Cust_Expense) by month so that we read all customers for a specific month and then read the same monthly table ONCE to pull all the records from that month, so that it does not loop 250,000 times.

@Joe, you are saying that asking (in the form of a question, not criticism) for a sample desired output, so that I can try to provide a solution that outputs the desired result, is indication of not understanding how the review process is supposed to work? — user2877959
@user2877959 No, that was in response to other comments that were before/after yours. They've been moderator deleted, it looks like. Your response is good (and your edit helpful). — Joe

user2877959 user2877959 · Accepted Answer · 2017-11-13T13:38:09

Depending on what you want the result to be, you can create one output per month by filtering on cust_expenses per month and joining with the corresponding monthly dataset

%macro want;
proc sql noprint;
select distinct month
into :months separated by ' '
from cust_expenses
;
quit;

proc sql;
%do i=1 %to %sysfunc(countw(&months));
  %let month=%scan(&months,&i,%str( ));
    create table want_&month. as
    select *
    from cust_expense(where=(month="&month.")) t1
    inner join cust_&month. t2
      on t1.customer_number=t2.customer_number
    ;
%end;
quit;
%mend;
%want;

Or you could have one output using one join by 'unioning' all those monthly datasets into one and dynamically adding a month column.

%macro want;
proc sql noprint;
select distinct month
into :months separated by ' '
from cust_expenses
;
quit;

proc sql;
  create table want as
  select *
  from cust_expense t1
  inner join (
              %do i=1 %to %sysfunc(countw(&months));
                %let month=%scan(&months,&i,%str( ));
                %if &i>1 %then union;
                select *, "&month." as month
                from cust_&month
              %end;
             ) t2
    on t1.customer_number=t2.customer_number
     and t1.month=t2.month
  ;
quit;
%mend;
%want;

In either case, I don't really see the point in joining those monthly datasets with the cust_expense dataset. The latter does not seem to hold any information that isn't already present in the monthly datasets.

SAS Macro help to loop monthly sas datasets

3 Answers