proc sql; select membertype, avg(milestraveled) as AvgMilesTraveled from sasuser.frequentflyers group by membertype; |
|
If a summary function...
|
Then the calculation
is...
|
Example
|
---|---|---|
specifies one column
as argument
|
performed down the column
|
proc sql;
select avg(salary)as AvgSalary
from sasuser.payrollmaster; |
specifies multiple columns
as arguments
|
performed across columns
for each row
|
proc sql outobs=10;
select sum(boarded,transferred,nonrevenue)
as Total
from sasuser.marchflights; |
If a GROUP BY clause...
|
Then PROC SQL...
|
Example
|
---|---|---|
is not present in the
query
|
applies the function
to the entire table
|
proc sql outobs=10;
select jobcode, avg(salary)
as AvgSalary
from sasuser.payrollmaster; |
is present in the query
|
applies the function
to each group specified in the GROUP BY clause
|
proc sql outobs=10; select jobcode, avg(salary) as AvgSalary from sasuser.payrollmaster group by jobcode; If a query contains
a GROUP BY clause, all columns in the SELECT clause that do not contain
a summary function should be listed in the GROUP BY clause or unexpected
results might be returned.
|
If a SELECT clause...
|
Then PROC SQL...
|
Example
|
---|---|---|
contains summary function(s)
and no columns outside of summary functions
|
calculates a single
value by using the summary function for the entire table or, if groups
are specified in the GROUP BY clause, for each group combines or rolls
up the information into a single row of output for the entire table
or, if groups are specified, for each group
|
proc sql;
select avg(salary)
as AvgSalary
from sasuser.payrollmaster; |
contains summary function(s)
and additional columns outside of summary functions
|
calculates a single
value for the entire table or, if groups are specified, for each group,
and displays all rows of output with the single or grouped value(s)
repeated
|
proc sql; select jobcode, gender, avg(salary) as AvgSalary from sasuser.payrollmaster group by jobcode,gender; |
proc sql;
select avg(salary) as AvgSalary
from sasuser.payrollmaster;
proc sql outobs=10;
select sum(boarded,transferred,nonrevenue)
as Total
from sasuser.marchflights;
proc sql outobs=20;
select avg(salary) as AvgSalary
from sasuser.payrollmaster;
proc sql outobs=20;
select jobcode, avg(salary) as AvgSalary
from sasuser.payrollmaster;
proc sql;
select jobcode,
avg(salary) as AvgSalary format=dollar11.2
from sasuser.payrollmaster
group by jobcode;
Using this form of COUNT...
|
Returns...
|
Example
|
---|---|---|
COUNT(*)
|
the total number of
rows in a group or in a table
|
select count(*) as Count |
COUNT(column)
|
the total number of
rows in a group or in a table for which there is a nonmissing value
in the selected column
|
select count(jobcode) as Count |
COUNT(DISTINCT column)
|
the total number of
unique values in a column
|
select count(distinct jobcode) as Count |
proc sql;
select count(*) as Count
from sasuser.payrollmaster;
proc sql; select substr(jobcode,1,2) label='Job Category', count(*) as Count from sasuser.payrollmaster group by 1;
JobCategory
,
is created by using the SAS function SUBSTR to extract the two-character
job category from the existing JobCode field. The second column, Count,
is created by using the COUNT function. The GROUP BY clause specifies
that the results are to be grouped by the first defined column (referenced
by 1 because the column was not assigned a name).
proc sql;
select count(distinct jobcode) as Count
from sasuser.payrollmaster;
proc sql;
select distinct jobcode
from sasuser.payrollmaster;
proc sql;
select jobcode,
avg(salary) as AvgSalary
format=dollar11.2
from sasuser.payrollmaster
group by jobcode;
proc sql;
select jobcode,
avg(salary) as AvgSalary
format=dollar11.2
from sasuser.payrollmaster
group by jobcode
having avg(salary) > 56000;
having AvgSalary > 56000
Note
that you do not have to specify the keyword CALCULATED in a HAVING
clause; you would have to specify it in a WHERE clause.proc sql; select empid, salary, (salary/sum(salary)) as Percent format=percent8.2 from sasuser.payrollmaster where jobcode contains 'NA';
NOTE: The
query requires remerging summary statistics back with the original
data. |
proc sql; select empid, salary, (salary/sum(salary)) as Percent format=percent8.2 from sasuser.payrollmaster where jobcode contains 'NA';
NA
',
PROC SQL calculates and returns the value of the SUM function (specified
in the SELECT clause).
NA
'. PROC SQL also uses
the result from the SUM function to calculate the arithmetic expression
(salary/sum(salary)).