## aggregate frequencies

Solved
Frequent Contributor
Posts: 136

# aggregate frequencies

``````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

Accepted Solutions
Solution
‎10-12-2016 11:32 AM
Super User
Posts: 23,713

## Re: aggregate frequencies

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;

All Replies
Solution
‎10-12-2016 11:32 AM
Super User
Posts: 23,713

## Re: aggregate frequencies

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;

Frequent Contributor
Posts: 136

## Re: aggregate frequencies

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

Super User
Posts: 23,713

## Re: aggregate frequencies

@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;``````

Occasional Contributor
Posts: 12

## Re: aggregate frequencies

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

☑ This topic is solved.