DATA Step, Macro, Functions and more

aggregate frequencies

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 110
Accepted Solution

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: 17,784

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;

View solution in original post


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

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: 110

Re: aggregate frequencies

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

Super User
Posts: 17,784

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 299 views
  • 1 like
  • 3 in conversation