Sunday, December 29, 2013

Pivot Tables using SAS

Excel Pivot tables through SAS
Quick Tip

Objective: Hello readers. Pivot table in Excel is an important tool which is used heavily and which makes lot of people’s life much simpler in doing a quick analysis on small data.
It saves the effort to go into SAS create a dataset and start writing code for each scenario. The drag and drop is even more intuitive and easily customizable.
SO I thought of sharing this quick tip to get you started with creating pivot tables directly from your preexisting program which creates the report.

What is a Pivot Table?

I am not a pivot table expert and do not intend to explain Pivot tables here but just for the definition’s sake according to Wikipedia
“In data processing, a pivot table is a data summarization tool found in data visualization programs such as Excel. Among other functions, a pivot-table can automatically sort, count, total or give the average of the data stored in one table or spreadsheet. It displays the results in a second table (called a "pivot table") showing the summarized data.”

Why use SAS to create one?

Let me tell you one scenario. In SAS reporting mostly we create a final dataset after data summarization which we export to Excel, format it and send to client. But what if your client knows a bit of Excel(which will be the case mostly) he will be happier to get a Pivot table in which he can do a bit of drag and drop to see other metrics for a quick check which you did not deliver(coz you were not supposed to).

Or many times you deliver a few Pivot tables showing different Metrics from the same data.

In both the cases if you automated SAS code creates various Pivots directly without you exporting the data and creating tables manually, all you just have to do is quick checks before sending it forward.

Yes yes I heard that…I know there are many ways to do the same thing. I just liked it, did that on one of my for  my boss and he was I thought maybe you could gain some extra points as well and there is almost no effort involved.

The code:

Sunday, May 26, 2013

An elegant use of call execute-Solution

An elegant use of call execute

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.

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.

Wednesday, May 1, 2013

How to create a Cartesian product using a datastep ?

How to create a Cartesian product using a datastep ?

Objective : Hello readers. This post is unlike all my previous posts which deal with one or the other concepts of SAS and explain them in detail. The idea for this post came up from one of the interview questions which I have been asked many times over (and I am asking it now when I am on the other side of the table :-) ).

In one of my previous posts I have compared the datastep merges with SQL joins, but no one ever tells how to do in datastep what is called a Cartesian product in SQL, and which is pretty easy to do in SQL. Interestingly Cartesian product forms tha base of SQl joins, so if we can replicate SQL joins in datastep then we should also be able to replicate Cartesian product..!!

The answer is Yes we can do it, and easily. But you’ll know it only when you see it.
So here is the short and simple way to do it, but before that let me explain for people who don’t know about Cartesian product, what it is and what is a point option(Shhh..this is the key)

Monday, April 22, 2013

SAS Date and Time functions(Part 2)

SAS Date and Time functions(Part 2)
A quick reference

Objective : This post is the second in the series of posts on functions and Second of two posts on Date Time functions. The first post was on SAS character functions. This post highlights frequently used SAS Date and time functions and provides a quick reference list and is invaluable for revision before interviews. Also this posts aim to strengthen the your function knowledge by providing interesting examples.

Date time functions:

MDY  Function:

This function takes month, day and year as numeric values and returns a SAS date value created from them.
data _null_;
      format sasdt1 date9.;
      put sasdt1=;


YEAR Function: