Transposing and summing dataset help?

[Krueger]

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;

[Krueger · Accepted solution]

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;

[ballardw]

What are you going to do with that data set?

If the purpose is a report for people to read then you might consider:

proc report data=have;
   columns primarykey secondarykey frequency type,percent;
   define secondarykey/group;
   define type /across;
run;

And if your data as shown in the "have" comes from some other summary you may be able to get to this sort of a report without that summary step.

I am uncomfortable reporting 0 percent when there is no value. Percentages usually come from something that is X / Y or (count of y with x property/ count of y). 0 percent implies the denominator is present and the numerator is 0. I am not sure that is the case for you data but you could use options missing='0' to display 0, or get more creative with a custom format.

[Krueger]

I ran proc freq to get frequencies of use by PrimaryKey. Most PrimaryKey's (there are thousands in the real data) will have SecondaryKeys and I want to know how often the types were used. In this case, knowing Type 1 & Type 2 is relevant as it's more of a True/False code. The second part of this that I'm working on now is getting the overall frequency and percent just by PrimaryKey and appending (I think this is the correct term) to the above dataset.

What I'm envisioning is the below although the way I'm doing now it's going to have the last Total column populated for each row where type=1 (13/15). 

Edit: I tested your proc report and that also works great! Wasn't even aware of that. Would it be possible to add the Overall Percent for Type 1 within this as well now?