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.
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.
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.
Perfect. Thank you!
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.