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
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
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
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
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
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
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_;