An elegant use of call execute-Solution

An elegant use of call execute

Solution

Objective : Hello readers. This post provides the solution of my previous post. If you have not read that post please go through it to understand the problem that is being solved here.

The previous post can be found here.

The solution:

So my dear readers, 2 days are over and as promised here is the solution.

Now when we have understood the question and the output required, let us look at the solution.

Below is the code I wrote to solve the problem, I will explain it step by step.

/* Macro to create a single account dataset will all transactions of that account */

%macro subset(account_id);

    data trans_&account_id.;

         set transactions;

         where account_id=&account_id.;

    run;

%mend;

/* Sorting transaction dataset before By processing */

proc sort data=transactions;

    by account_id;

run;

/* For every new account id call the subset macro */

data _null_;

    set transactions;

    by account_id;

    if first.account_id then

         call execute('%subset('||account_id||')');

run;

Explanation:

I guess the code is self-explanatory but anyway I’ll explain it step by step.

STEP 1 : The macro subset

This macro contains a simple datastep which just subsets the observation from the complete transaction dataset using a where statement.

For e.g. For account id 111 the code generate with this macro is :

    data trans_111;

         set transactions;

         where account_id=111;

    run;

So this code will create a dataset transa_111 and will keep only transaction where account id is 111. This is what is required. So what is left is calling this macro for every account id dynamically as every time the accounts will change. step 3 does just that.

STEP 2 : Sorting

Step 2 is only required because we will be doing by group processing in the next step. So sorting the dataset.

STEP 3 : The call

This step achieves the dynamic call to subset macro using call execute in a datastep. We set the transaction dataset using account id as by variable and whenever the first in group or to say new account is encountered, we use call execute to subset macro passing the current account id as an input parameter. The macro does the rest. Thats it.

Pretty simple right..!!

Conclusion:

This was my way of tackling the question, tell me what is yours in the comments below. Will be very grateful to learn from you folks.

Will be back with some more SAS magic. Goodbye till then.

Saurabh Singh  Chauhan

(er.chauhansaurabh@gmail.com)         

Note: Comments and suggestions are always welcome.