Here we illustrate how to collapse data across
observations using proc means. Our example uses a hypothetical data set containing
information about kids in three families. These examples show how you can collapse
across kids to form family records from the kids records.
1. Reading the data file
Here is the SAS program that makes a data file called kids.
It contains three families (famid) each with three kids. It contains the
family ID, the name of the kid, the order of birth (1 2 3 for 1st, 2nd, 3rd), and the age,
weight and sex of each kid.
DATA kids; LENGTH kidname $ 4 sex $ 1; INPUT famid kidname birth age wt sex ; CARDS; 1 Beth 1 9 60 f 1 Bob 2 6 40 m 1 Barb 3 3 20 f 2 Andy 1 8 80 m 2 Al 2 6 50 m 2 Ann 3 2 20 f 3 Pete 1 6 60 m 3 Pam 2 4 40 f 3 Phil 3 2 20 m ; RUN; PROC PRINT DATA=kids; RUN;
Here is the output of the proc print.
OBS KIDNAME SEX FAMID BIRTH AGE WT 1 Beth f 1 1 9 60 2 Bob m 1 2 6 40 3 Barb f 1 3 3 20 4 Andy m 2 1 8 80 5 Al m 2 2 6 50 6 Ann f 2 3 2 20 7 Pete m 3 1 6 60 8 Pam f 3 2 4 40 9 Phil m 3 3 2 20
2. Using proc means to collapse data across records
We can use proc means to collapse
across across families. The example below computes the average age of each child
within each family (because of the class famid; statement) and then
outputs the results into a SAS data file called fam2.
PROC MEANS DATA=kids ; CLASS famid; VAR age; OUTPUT OUT=fam2 MEAN= ; RUN;
The output of the proc means is shown
below.
FAMID N Obs N Mean Std Dev Minimum Maximum
---------------------------------------------------
1 3 3 6.000 3.00000 3.00000 9.0000000
2 3 3 5.333 3.05505 2.00000 8.0000000
3 3 3 4.000 2.00000 2.00000 6.0000000
----------------------------------------------------
And we use proc print to have a look at fam2.
PROC PRINT DATA=fam2; RUN;
And this output shows that the data file fam2
contains the average of age for the kids for each family.
OBS FAMID _TYPE_ _FREQ_ AGE 1 . 0 9 5.11111 2 1 1 3 6.00000 3 2 1 3 5.33333 4 3 1 3 4.00000
However, there is one extra record (the one shown
below). This is the overall mean (notice that the _FREQ_ for it is 9, and there are
a total of nine kids). We really don’t want this record.
OBS FAMID _TYPE_ _FREQ_ AGE 1 . 0 9 5.11111
We can suppress the creation of the record with the
overall mean with the nway option on the proc means
statement. In general, when you use proc means with the class
statement and make an output data file, you usually will want to use the
nway
option as shown below.
PROC MEANS DATA=kids NWAY ; CLASS famid; VAR age; OUTPUT OUT=fam3 MEAN= ; RUN;[we omit the proc means output]
PROC PRINT DATA=fam3; RUN;
Now, the fam3 data file had just has
three
records with the average age for each family.
OBS FAMID _TYPE_ _FREQ_ AGE 1 1 1 3 6.00000 2 2 1 3 5.33333 3 3 1 3 4.00000
3. Explicitly naming the collapsed variables
The following proc means example does
the exact same thing as the prior example, except that the average of age
is explicitly named, calling it avgage.
PROC MEANS DATA=kids NWAY ; CLASS famid; VAR age; OUTPUT OUT=fam4 MEAN=avgage ; RUN;[we omit the proc means output]
PROC PRINT DATA=fam4; RUN;
The output is the same as before, except that the
average of age is called avgage.
OBS FAMID _TYPE_ _FREQ_ AVGAGE 1 1 1 3 6.00000 2 2 1 3 5.33333 3 3 1 3 4.00000
The rest of the examples will explicitly name the
collapsed variables (e.g., use mean=avgage instead of just mean=
). In general, it is better to explicitly name the variables to avoid confusion
between the original variable and the collapsed variable.
4. Getting means of more than one variable
We can request averages for more than one variable. Here
we get the average for age and for wt all in the same
command.
PROC MEANS DATA=kids NWAY ; CLASS famid; VAR age wt; OUTPUT OUT=fam5 MEAN=avgage avgwt; RUN;FAMID N Obs Variable N Mean Std Dev Minimum ----------------------------------------------------------- 1 3 AGE 3 6.0000 3.0000000 3.0000000 WT 3 40.0000 20.0000000 20.0000000 2 3 AGE 3 5.3333 3.0550505 2.0000000 WT 3 50.0000 30.0000000 20.0000000 3 3 AGE 3 4.0000 2.0000000 2.0000000 WT 3 40.0000 20.0000000 20.0000000 ------------------------------------------------------------ [to save space, we omit the output with the maximum of age and wt] PROC PRINT DATA=fam5; RUN;
As you see in the output below, avgage
is the average age and avgwt is the average weight of the kids in each
family.
OBS FAMID _TYPE_ _FREQ_ AVGAGE AVGWT 1 1 1 3 6.00000 40 2 2 1 3 5.33333 50 3 3 1 3 4.00000 40
5. Requesting multiple statistics at once
We can request multiple statistics at once. The
command below gets the mean, standard deviation and age (mean std
and N) for age and wt within each
family.
PROC MEANS DATA=kids NWAY; CLASS famid; VAR age wt; OUTPUT OUT=fam6 MEAN=avgage avgwt STD=stdage stdwt N=nage nwt ; RUN;
The results below shows the output of the proc means.
FAMID N Obs Variable N Mean Std Dev Minimum -------------------------------------------------------------- 1 3 AGE 3 6.0000000 3.0000000 3.0000000 WT 3 40.0000000 20.0000000 20.0000000 2 3 AGE 3 5.3333333 3.0550505 2.0000000 WT 3 50.0000000 30.0000000 20.0000000 3 3 AGE 3 4.0000000 2.0000000 2.0000000 WT 3 40.0000000 20.0000000 20.0000000 -------------------------------------------------------------- [to save space, we omit the output with the maximum of age and wt]PROC PRINT DATA=fam6; RUN;
The results below correspond to the proc means
above. You can see that the average age and wt by
family are in avgage and avgwt. Likewise stdage
and stdwt contain the standard deviation of age and wt
for each family, and nage and nwt have the valid number
of observations for age and wt for each family.
OBS FAMID _TYPE_ _FREQ_ AVGAGE AVGWT STDAGE STDWT NAGE NWT 1 1 1 3 6.00000 40 3.00000 20 3 3 2 2 1 3 5.33333 50 3.05505 30 3 3 3 3 1 3 4.00000 40 2.00000 20 3 3
6. Suppressing proc means output
In our example, we have just three families. For your data,
you might have dozens, hundreds, or thousands of families (or whatever grouping you are
using). The output of the proc means can get very long, so you may
want to suppress the output. You can do that with the noprint option as
shown below.
PROC MEANS DATA=kids NWAY NOPRINT ; CLASS famid; VAR age wt; OUTPUT OUT=fam7 MEAN=avgage avgwt STD=stdage stdwt N=nage nwt ; RUN;
The output from the proc means is not
printed due to the noprint option.
7. Counting the number of boys and girls in the family
Suppose you wanted a count of the number of boys
and
girls in the family. We can do that with one extra step. We will
make a dummy variable that is 1 if a boy (0 if not), and a dummy variable that is 1
if a girl (and 0 if not). The sum of the boy dummy
variable within a family is the number of boys in the family and the sum of the
girl
dummy variable within a family is the number of girls in the family.
First, we use a data step to make the boy
and girl dummy variable.
DATA kids2 ; SET kids; If sex = "m" THEN boy = 1; ELSE boy = 0 ; If sex = "f" THEN girl= 1; ELSE girl= 0 ; RUN;
We use proc print to look at the boy and girl variables to double check them.
PROC PRINT DATA=kids2; VAR sex boy girl ; RUN;OBS SEX BOY GIRL 1 f 0 1 2 m 1 0 3 f 0 1 4 m 1 0 5 m 1 0 6 f 0 1 7 m 1 0 8 f 0 1 9 m 1 0
We use proc means to sum up the boy
and girl dummy variables for each family and to create a data file called
fam8 that contains the sum of boy in boys
and the sum of girl in girls. We use the noprint
option to suppress the output of the proc means.
PROC MEANS DATA=kids2 NWAY NOPRINT ; CLASS famid; VAR boy girl ; OUTPUT OUT=fam8 SUM=boys girls ; RUN;
We do a proc print to look at the
output data file.
PROC PRINT DATA=fam8; RUN;
As we expect, the proc print shows that
boys contains the count of boys in each family and girls
contains the count of girls in each family.
OBS FAMID _TYPE_ _FREQ_ BOYS GIRLS 1 1 1 3 1 2 2 2 1 3 2 1 3 3 1 3 2 1
8. Merging the collapsed data back with the original data
Sometimes you want to merge the collapsed
data back with the original data. Let’s use an example creating avgage and
avgwt for each family, then merge those results back with the original kids data.
First, let’s collapse the data across families to make avgage and avgwt just as we have done before.
PROC MEANS DATA=kids NWAY NOPRINT ; CLASS famid; VAR age wt; OUTPUT OUT=fam9 MEAN=avgage avgwt; RUN;
Second, we sort kids and sort fam9 both on famid, preparing for merging them together.
PROC SORT DATA=kids OUT=skids ; BY famid ; RUN; PROC SORT DATA=fam9 OUT=sfam9 ; BY famid ; RUN;
Third, we merge the sorted files together (skids and
sfam9) by famid. We can drop _type_ and _freq_ since they are not needed, but we don’t have to
drop them.
DATA kidsmrg ; MERGE skids sfam9 ; BY famid ; DROP _type_ _freq_ ; RUN;
We can print out the results, showing that the variables avgage and avgwt are now merged back with the original
kids so each kid has the associated average age and weight for their family.
PROC PRINT DATA=kidsmrg; RUN; OBS KIDNAME SEX FAMID BIRTH AGE WT AVGAGE AVGWT 1 Beth f 1 1 9 60 6.00000 40 2 Bob m 1 2 6 40 6.00000 40 3 Barb f 1 3 3 20 6.00000 40 4 Andy m 2 1 8 80 5.33333 50 5 Al m 2 2 6 50 5.33333 50 6 Ann f 2 3 2 20 5.33333 50 7 Pete m 3 1 6 60 4.00000 40 8 Pam f 3 2 4 40 4.00000 40 9 Phil m 3 3 2 20 4.00000 40
9. Problems to look out for
- You may end up with records that you were not expecting
if you forget to use the nway option. - If you collapse across records, and then remerge back
with the original data, be sure that you explicitly name the variables when you collapse
them. If you don’t, the variables from the collapsed data will have the same names
as the original data, and they will clash when you remerge the data.
10. For more information
-
For more information about merging data
files, see the SAS Learning Module on Match Merging Data Files
in SAS.