aggregating frequencies

[MillerEL]

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. 

[Reeza]

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.

[MillerEL]

Perfect. Thank you!

[Astounding · Accepted solution]

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.

[MillerEL]

Perfect! Thank you!