Operation similar to pivot table in SAS DI

[PatrykSAS]

Hi,

I'd like to make a calculation similar to pivot table in SAS (using Data Integration studio). My data looks like follows:

ID TYPE

1  NEW

1  NEW

1  CHURN

2  NEW

2  CHURN

3  WINBACK

I want to turn it into the following form:

ID NEW CHURN WINBACK

1   2       1             0

2   1       1             0

3   0       0             1

Can you help me with simple solution?

[LaurieF · Accepted solution]

The SAS method for pivot tabling is proc transpose, and under the Data Transformations you'll see the Transpose transformation.

Here's Base code that will (mostly) do what you want:

data from;
infile cards firstobs=2;
attrib id length=3;
attrib type length=$ 7;
input id type;
cards;
ID TYPE
1  NEW
1  NEW
1  CHURN
2  NEW
2  CHURN
3  WINBACK
;
run;

proc sql;
create table to as
  select id,
         type,
		 count(*) as count
    from from
	group by id, 
             type;
quit;

proc transpose data=to out=to_trans(drop=_name_);
by id;
id type;
var count;
run;

option missing='0';
proc print data=to_trans noobs width=min;
run;

Note that I setting missing to '0' at the end. This is because there are obviously values in the transposed dataset which don't have a value; proc print would otherwise print them as missing. If you want them as zero in the dataset, you'll have to do some post-processing.

So as you can see, you'll have to process your dataset twice: once to do the counts (SQL Extract transformation), then rotating it (Data Transpose).

[PatrykSAS]

It works! Thank you

[LaurieF]

It's a pleasure.

It can be tricky mapping the id columns with the transpose transformation. I find that, if I don't already know the cardinality of type, I run the job, refresh the metadata and see what's what. It all depends on what you want to do with the data afterwards.