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.
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.