You can use the HAVING clause within PROC SQL in SAS to filter for rows that meet a certain condition.
Note the subtle difference between the WHERE and HAVING clause:
- WHERE filters rows before any grouping occurs.
- HAVING filters rows after any grouping occurs.
The following example shows how to use the HAVING clause in practice.
Example: How to Use HAVING Clause in PROC SQL
Suppose we have the following dataset in SAS that contains information about various basketball players:
/*create dataset*/
data my_data;
input team $ position $ points;
datalines;
A Guard 22
A Guard 20
A Guard 30
A Forward 14
A Forward 11
B Guard 12
B Guard 22
B Forward 30
B Forward 9
B Forward 12
B Forward 25
C Guard 22
C Guard 19
C Guard 10
;
run;
/*view dataset*/
proc print data=my_data;
We can use the following code to calculate the sum of points scored by players on each team WHERE the players are Guards and then filter the results to only show teams HAVING a sum greater than 50:
proc sql;
select team, sum(points) as sum_points
from my_data
where position='Guard'
group by team
having sum_points>50;
quit;
Here is exactly how this code worked:
- First, we used SELECT to select team and the sum of points
- Then, we used WHERE to filter for rows where position was ‘Guard’
- Then, we used GROUP to group the results by team
- Then we used HAVING to filter for teams with sum of points > 50
Here is what the results of this query would have looked like if we didn’t include the HAVING statement:
proc sql;
select team, sum(points) as sum_points
from my_data
where position='Guard'
group by team;
quit;
Notice that the sum of points for team B was not greater than 50.
Thus, when we used the HAVING statement in the previous example, we filtered out team B since their sum of points was not greater than 50.
Additional Resources
The following tutorials explain how to perform other common tasks in SAS:
SAS: How to Use LIKE Operator in PROC SQL
SAS: How to Use the IN Operator in PROC SQL
SAS: How to Use the WHERE Operator in PROC SQL