DATA Step, Macro, Functions and more

aggregate frequencies

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 128
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: 19,822

Re: aggregate frequencies

Posted in reply to lillymaginta

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: 19,822

Re: aggregate frequencies

Posted in reply to lillymaginta

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

Re: aggregate frequencies

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

Super User
Posts: 19,822

Re: aggregate frequencies

Posted in reply to lillymaginta

@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
  • 368 views
  • 1 like
  • 3 in conversation