Wednesday, May 22, 2013

An elegant use of call execute


An elegant use of call execute

Objective : Hello readers. This post aims at solving a typical interview question in an elegant manner using call execute. This question is not really tough but can be a tough nut to crack for an uninformed candidate.
Please note that this post is not an explanation of the working of call execute. For that I will write another post sometime in future.

So the questions we are tackling here goes like this :

I have a transaction dataset which stores all the transactions done by all the bank’s customers in the past month. Now I want you to create a separate dataset for each account id which should contain all the transaction done for that account.

For. E.g. I have 5 account ids and the transaction dataset contains all the 300 transactions done for these 5 accounts. So as final output I want 5 datasets by the name of the account ids and each of those dataset must contains all the transactions for just that account id.



The data:
We will be using the following dummy data for the exercise:



For your convenience here is the code to create that:

data transactions;
input account_id trans_id amount;
datalines;
111 1 250
111 2 567
111 3 7890
222 1 2250
222 2 578
333 1 8965
444 1 74216
444 2 154
555 1 4879
;
run;

The output:
There are 5 different accounts in the data, so the output will be 5 datasets(data_111, data_222,...) and each dataset will contain its own transactions.

For e.g. Data_111 will have the following 3 observations:

111 1 250
111 2 567
111 3 7890

ALERT..!!!

Why don’t you try solving it yourself before looking at the solution. It’s not mandatory to use call execute, there are 10 different ways.
By trying it You’ll get to know the difficulty and will be able to appreciate the elegance of the solution more so.

The solution:

I will not post the solution right away, please try it yourself and post your answers in comments below. Maybe you will have an even better code to solve it and I can learn from your code. I will post my solution in 2 days.

The solution can be found here (Link coming up in 2 days)

Conclusion:

Please post your solution as comments so that I can learn from you experts. 

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.