topic Operation similar to pivot table in SAS DI in SAS Programming

Operation similar to pivot table in SAS DI

PatrykSAS — Tue, 08 Sep 2020 13:53:46 GMT

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 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?

Re: Operation similar to pivot table in SAS DI

LaurieF — Tue, 08 Sep 2020 21:39:54 GMT

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).

Re: Operation similar to pivot table in SAS DI

PatrykSAS — Wed, 09 Sep 2020 08:50:11 GMT

It works! Thank you

Re: Operation similar to pivot table in SAS DI

LaurieF — Wed, 09 Sep 2020 08:53:08 GMT

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.