topic Proc Transpose / Cross Tab question in SAS Procedures

Proc Transpose / Cross Tab question

deleted_user — Mon, 15 Jun 2009 22:14:25 GMT

Hi. Can you please help me with the following issue...

The Data looks like this:
cust_id acct hierarchy amt
1 123 aa $100
1 125 bb $250
1 121 cc $125
2 234 aa $100
2 254 aa $300
3 333 aa $200
3 345 bb $250
3 365 bb $210
3 344 cc $75

and it needs to look like this:
cust_id..acct....aa....bb.....cc.....total
1...........123....$100.................$100
1...........125...........$250..........$250
1...........121..................$125...$125
2...........234....$100.................$100
2...........254....$300.................$300
3...........333....$200.................$200
3...........345....$250.................$250
3...........365............$210.........$210
3...........344...................$75...$75
Some points about the data -
1. each acct will only have one amt, so the [total] value in the new dataset equals the amt and will not be a summation.
2. there will be over 50 different hierarchy values and they cannot be hard coded.

Thank you for your help.

Re: Proc Transpose / Cross Tab question

deleted_user — Mon, 15 Jun 2009 23:40:33 GMT

This is simple with data step logic. See the below code. Does it solve your problem ?

proc sql noprint;
select distinct(hierarchy) into: var separated by ' ' from test;
select count(distinct hierarchy) into : N from test;
quit;

proc sort data=test;
by cust_id hierarchy;
run;

%macro dummy;
data test2(rename=(amt=total) drop=hierarchy);
set test;
%do i=1 %to &N;
%let var1=%scan("&var", &i, " ");
if hierarchy="&var1" then &var1=amt;
%end;
run;
%mend dummy;

%dummy;

~ Sukanya E Message was edited by: Sukanya

Re: Proc Transpose / Cross Tab question

Cynthia_sas — Tue, 16 Jun 2009 02:17:21 GMT

Hi:
If the data is as you describe, then either PROC TABULATE or PROC REPORT would be another way to do this. I'd probably use PROC REPORT because of the requirement to repeat the CUST_ID on every report row.

cynthia
[pre]
data cust;
infile datalines;
input cust_id acct hierarchy $ amt : comma4.;
return;
datalines;
1 123 aa $100
1 125 bb $250
1 121 cc $125
2 234 aa $100
2 254 aa $300
3 333 aa $200
3 345 bb $250
3 365 bb $210
3 344 cc $75
;
run;

ods listing close;
ods html file='c:\temp\crosstab.html' style=sasweb;
proc tabulate data=cust f=dollar6.;
title 'Proc Tabulate';
class cust_id acct hierarchy /order=data;
var amt;
table cust_id * acct,
hierarchy*amt=' ' all*amt=' ';
keylabel sum=' ';
run;

proc report data=cust nowd;
title 'Proc Report';
column cust_id show_cust acct amt,hierarchy amt=alltot;
define cust_id / order order=data noprint;
define show_cust / computed 'Cust/ID';
define acct / order order=data;
define amt / sum 'Hierarchy' f=dollar6.;
define hierarchy / across ' ' ;
define alltot / sum f=dollar6.;
compute before cust_id;
hold = cust_id;
endcomp;
compute show_cust ;
show_cust=hold;
endcomp;
run;
ods html close;
[/pre]

Re: Proc Transpose / Cross Tab question

deleted_user — Tue, 16 Jun 2009 19:44:05 GMT

Thank you very much! This is exactly what I needed.