Collapsing across observations using proc sql

http://saslearn.blogspot.in/2012/06/collapsing-across-observations-using.html

1. Creating a new variable of grand mean
Let's say that we have a data set containing three families with kids and we want to create a new variable in the data set that is the grand mean of age across the entire data set. This can be accomplished by using SAS proc sql as shown below. We also print out the new data set with a new variable of grand mean using proc print.
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 sql;
create table kids1 as
select *, mean(age) as mean_age
from kids;
quit;

proc print data=kids1 noobs;
run;

kidname sex famid birth age wt mean_age

Beth f 1 1 9 60 5.11111
Bob m 1 2 6 40 5.11111
Barb f 1 3 3 20 5.11111
Andy m 2 1 8 80 5.11111
Al m 2 2 6 50 5.11111
Ann f 2 3 2 20 5.11111
Pete m 3 1 6 60 5.11111
Pam f 3 2 4 40 5.11111
Phil m 3 3 2 20 5.11111

http://saslearn.blogspot.in/2012/06/collapsing-across-observations-using.html

2. Creating a new variable of group mean


We will continue to use the data set in previous example. Now we want to use the variable famid as a group variable and create a new variable that is the group mean of the variable age.
proc sql;
create table kids2 as
select *, mean(age) label="group average" as mean_age
from kids
group by famid;
quit;

title 'New Variable of Group Mean';
proc print data=kids2 noobs;
run;

title 'Label at Work';
proc freq data=kids2;
table mean_age;
run;
Now we see that in the following output of proc print the new variable of group mean we just created. We also see the label created for the variable in the output of proc freq.
New Variable of Group Mean

kidname sex famid birth age wt mean_age

Barb f 1 3 3 20 6.00000
Bob m 1 2 6 40 6.00000
Beth f 1 1 9 60 6.00000
Ann f 2 3 2 20 5.33333
Al m 2 2 6 50 5.33333
Andy m 2 1 8 80 5.33333
Pete m 3 1 6 60 4.00000
Phil m 3 3 2 20 4.00000
Pam f 3 2 4 40 4.00000

Label at Work
The FREQ Procedure

group average

Cumulative Cumulative
mean_age Frequency Percent Frequency Percent
-------------------------------------------------------------------
4 3 33.33 3 33.33
5.3333333333 3 33.33 6 66.67
6 3 33.33 9 100.00


http://saslearn.blogspot.in/2012/06/collapsing-across-observations-using.html

3. Creating multiple variables of summary statistics at once


Sometimes we only need summary statistics based on a group variable similar to the output of proc means. This can also be done in proc sql as shown in our next example.
proc sql;
create table kids3 as
select famid, mean(age) as mean_age , std(age) as std_age,
mean(wt) as mean_wt, std(wt) as std_wt from kids
group by famid;
quit;
proc print data=kids3 noobs;
run;

famid mean_age std_age mean_wt std_wt

1 6.00000 3.00000 40 20
2 5.33333 3.05505 50 30
3 4.00000 2.00000 40 20
If you only want the output statistics instead of creating a new data set, you can omit the create table statement and simply run the proc sql part. The result will be shown in the output window.
proc sql;
select famid, mean(age) as mean_age, std(age) as std_age,
mean(wt) as mean_wt, std(wt) as std_wt from kids
group by famid;
quit;
From the Output Window:
famid mean_age std_age mean_wt std_wt
------------------------------------------------
1 6 3 40 20
2 5.333333 3.05505 50 30
3 4 2 40 20


http://saslearn.blogspot.in/2012/06/collapsing-across-observations-using.html

4. Creating multiple summary statistics variables in the original data set


proc sql;
create table fam5 as
select *, mean(age) as mean_age, std(age) as std_age,
mean(wt) as mean_wt, std(wt) as std_wt from kids
group by famid
order by famid, kidname desc;
quit;
proc print data=fam5;
run;

From the Output Window:

Obs kidname sex famid birth age wt mean_age std_age mean_wt std_wt

1 Bob m 1 2 6 40 6.00000 3.00000 40 20
2 Beth f 1 1 9 60 6.00000 3.00000 40 20
3 Barb f 1 3 3 20 6.00000 3.00000 40 20
4 Ann f 2 3 2 20 5.33333 3.05505 50 30
5 Andy m 2 1 8 80 5.33333 3.05505 50 30
6 Al m 2 2 6 50 5.33333 3.05505 50 30
7 Phil m 3 3 2 20 4.00000 2.00000 40 20
8 Pete m 3 1 6 60 4.00000 2.00000 40 20
9 Pam f 3 2 4 40 4.00000 2.00000 40 20

http://saslearn.blogspot.in/2012/06/collapsing-across-observations-using.html


5. Creating variables and their summary statistics on-the-fly


Let's say that we want to know the number of boys and girls in each family. We can use variable sex to figure it out in one step using proc sql as shown below.
proc sql;
create table my_count as
select famid, sum(boy) as num_boy, sum(girl) as num_girl from
(select famid, (sex='m') as boy, (sex='f') as girl from kids)
group by famid;
quit;
proc print data=my_count noobs;
run;

From the Output Window
famid num_boy num_girl

1 1 2
2 2 1
3 2 1


http://saslearn.blogspot.in/2012/06/collapsing-across-observations-using.html


6. Creating grand mean and save it into a SAS macro variable


Sometimes, we want to get a summary statistic for a variable and use it later for other purposes. We can save the summary statistic in a macro variable and then it can be accessed throughout the entire SAS session. proc sql is very handy as shown in the following example where we save the grand mean of variable age into macro variable meanage.
proc sql noprint;
select mean(age) into :meanage from kids;
quit;
%put &meanage;

From Log Window:

3027 proc sql noprint;
3028 select mean(age) into :meanage from kids;
3029 quit;
NOTE: PROCEDURE SQL used:
real time 0.00 seconds
cpu time 0.00 seconds

3030 %put &meanage;
5.111111

http://saslearn.blogspot.in/2012/06/collapsing-across-observations-using.html


7. Creating group means and save them into a sequence of SAS macro variables

proc sql noprint;
select mean(age) into :meanage1 - :meanage3 from kids
group by famid;
quit;
%put _user_;

http://saslearn.blogspot.in/2012/06/collapsing-across-observations-using.html