aggregating frequencies

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 14
Accepted Solution

aggregating frequencies

[ Edited ]

Hello,

 

I have a question about aggregating frequencies. In short, each observation has a family ID and a marker that says if the observation is for an adult or a child. (example data below). NOTE: the real dataset included 1000s of families. 

 

FamID adlt chld
001      1     0
001      0     1
001      0     1
002      1     0
002      1     0
002      0     1
002      0     1
002      0     1
002      0     1
003      1     0
003      0     1
003      0     1
004      1     0 
004      0     1
004      0     1

 

I need to determine how many families have 1 child, or 2 children, etc...

The output should look like the following:

 

#Child   #families

1            0

2            3

3            0

4            1

 

Can anyone offer advise on how to approach this? I tried the following without success:

 

PROC SUMMARY data=in;
class FamID;
var chld adlt;
output out=agg sum=chld adlt;
run;

 

 

PROC TABULATE data=in;
class FamID;
var chld;
table chld;
run;

 

I'm sure that I'm overthinking this, but I'm stuck. Any help would be appreciated. 

Thank you. 

 


Accepted Solutions
Solution
‎09-07-2017 02:09 PM
Super User
Posts: 6,349

Re: aggregating frequencies

Many ways to skin this cat ... I tend to favor PROC FREQ for counting.  Start by adding NWAY to the end of your PROC SUMMARY statement.  Then continue:

 

proc freq data=AGG;

tables chld;

run;

 

The CHLD column is the number of children, and the FREQUENCY column is the number of families having that number of children.

View solution in original post


All Replies
Super User
Posts: 22,425

Re: aggregating frequencies

Your approach is correct, but the data for proc tabulate should be the output from the PROC SUMMARY not the original data set. 

 

The logic is essentially:

 

1. Find the number of kids per family - add child numbers using PROC SUMMARY

2. Summarize data from Step 1 using PROC FREQ or PROC TABULATE. Note that you don't want the CLASS variable in the PROC TABULATE because you're counting overall, not by FAMILY ID.

 

 

Occasional Contributor
Posts: 14

Re: aggregating frequencies

Perfect. Thank you!

Solution
‎09-07-2017 02:09 PM
Super User
Posts: 6,349

Re: aggregating frequencies

Many ways to skin this cat ... I tend to favor PROC FREQ for counting.  Start by adding NWAY to the end of your PROC SUMMARY statement.  Then continue:

 

proc freq data=AGG;

tables chld;

run;

 

The CHLD column is the number of children, and the FREQUENCY column is the number of families having that number of children.

Occasional Contributor
Posts: 14

Re: aggregating frequencies

Posted in reply to Astounding
Perfect! Thank you!
☑ This topic is solved.

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

Discussion stats
  • 4 replies
  • 376 views
  • 6 likes
  • 3 in conversation