equivalent of a pivot table in sas

[sasprogramming]

I have the current set of data that looks like this:

ID	Score	PB
1	100	0.4
2	90	0.6
3	120	0.3
4	120	0.2
5	95	0.15
6	85	0.12
7	63	0.11
8	100	0.19
9	52	0.53
10	100	0.5
11	125	0.23
12	90	0.7

I want to summarise it by 'Score' like so:

Row LabelsCount of IDAverage of PB

52	1	0.53
63	1	0.11
85	1	0.12
90	2	0.65
95	1	0.15
100	3	0.363333333
120	2	0.25
125	1	0.23

How do I go about doing this in SAS? Thanks.

[PaigeMiller · Accepted solution]

proc summary data=have nway;
    class score;
    var pb;
    output out=stats mean=;
run;

[sasprogramming]

This works well thanks, but is there anyway to add a column that is the count, i.e. the frequency of ID?

Thanks.

[Tom]

---

@sasprogramming wrote:

This works well thanks, but is there anyway to add a column that is the count, i.e. the frequency of ID?

Thanks.

---

What is wrong with the count it already produces?

[sasprogramming]

Oh I understand now, thank you

[novinosrin]

Hi @sasprogramming 

The solution provided by @PaigeMiller  should have a variable called _freq_ in the output dataset. I think that's what you are looking for .

You could rename that as count

output out=stats (drop=_type_ rename=(_freq_=count)) mean= ;

---

@sasprogramming wrote:

This works well thanks, but is there anyway to add a column that is the count, i.e. the frequency of ID?

Thanks.

---