topic aggregating frequencies in SAS Programming

aggregating frequencies

MillerEL — Thu, 07 Sep 2017 17:26:29 GMT

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.

Re: aggregating frequencies

Reeza — Thu, 07 Sep 2017 17:29:44 GMT

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.

Re: aggregating frequencies

Astounding — Thu, 07 Sep 2017 17:42:16 GMT

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.

Re: aggregating frequencies

MillerEL — Thu, 07 Sep 2017 18:11:21 GMT

Perfect. Thank you!

Re: aggregating frequencies

MillerEL — Thu, 07 Sep 2017 18:11:33 GMT

Perfect! Thank you!