Unsure how to label the subject for this.
I basically want to transpose my data so that I have 1 column for the Frequency being the total for that particular group (PrimaryKey SecondaryKey).
I then want the Percents for each type as their own columns.
I tried Proc Transpose which gives me the frequencies listed out next to eachother (Good) but I get another row for the same SecondaryKeys with the Frequencies which I don't want.
data have;
input PrimaryKey SecondaryKey $1. Type Frequency Percent;
datalines;
1 a 1 1 100.00
1 b 1 7 87.50
1 b 2 1 12.50
1 c 1 2 100.00
1 d 1 2 66.67
1 d 2 1 33.33
1 e 1 1 100.00
;
run;
data want;
input PrimaryKey SecondaryKey $1. FrequencySum Type1_Percent Type2_Percent;
datalines;
1 a 1 100.00 0.00
1 b 8 87.50 12.50
1 c 2 0.00 100.00
1 d 3 66.67 33.33
1 e 1 100.00 0.00
;
run;
/*Attempts*/
proc transpose data=have out=want(drop=_name_);
by PrimaryKey SecondaryKey;
id Type;
var Frequency Percent;
run;
proc summary data=have nway missing;
class PrimaryKey SecondaryKey;
var Type Frequency Percent;
output out =want (drop=_type_ _freq_) sum=;
run;
Edit: I managed to figure it out just needed to do the SUM before transpose.
proc sql;
create table want as
select PrimaryKey, SecondaryKey, Type, SUM(Frequency) AS Total, Percent
from have
group by PrimaryKey, SecondaryKey
;
quit;
proc transpose data=want out=want1(drop=_name_);
by PrimaryKey SecondaryKey Total;
id Type;
var Percent;
run;
... View more
