aggregate frequencies

[lillymaginta]

id rx
1  a
1  b
1  d
2  a 
2  b
3  a
3 c

I have the following data, I am just trying to find the aggregate total per each patient regardless of rx, I want the following output:

id total

1   3

2  2

3  2

[Reeza · Accepted solution]

If you have no duplicate rx then use PROC FREQ. 

If you do have duplicate rx, use either double PROC FREQ or PROC SQL. 

proc SQL;

create table want as

select id, count(distinct rx) as num_rx

from have;

quit;

[lillymaginta]

Thank you! just to add you need a by rx statement after 

[Reeza]

@lillymaginta sorry, yes, should be GROUP BY ID in that SQL query.

@rbikes

The proc freq is relatively straightforward.

proc freq data=have noprint;
table id*rx/out=id_rx_count;
run;

proc freq data=id_rx_count noprint;
table id / out=by_id;
run;

proc print data=by_id;
run;

[rbikes]

Could you give an example of a the double freq you mentioned or point me some where that does?