Proc Transpose / Cross Tab question

[deleted_user]

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.

[deleted_user]

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

[Cynthia_sas]

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]

[deleted_user]

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