Table of Contents
The “SAS: Use HAVING Clause Within PROC SQL” feature allows users of the SAS language to utilize the HAVING clause within the PROC SQL procedure. This feature enables users to filter and group data within their SQL queries, similar to how the HAVING clause is used in other programming languages. By implementing the HAVING clause within PROC SQL, users can efficiently manipulate and analyze data to meet their specific needs. This feature is a valuable addition to the SAS language and enhances its capabilities for data analysis and manipulation.
SAS: Use HAVING Clause Within PROC SQL
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 printdata=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.
The following tutorials explain how to perform other common tasks in SAS:
Cite this article
stats writer (2024). Can the SAS language’s “SAS: Use HAVING Clause Within PROC SQL” feature be implemented by using the HAVING clause within PROC SQL?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/can-the-sas-languages-sas-use-having-clause-within-proc-sql-feature-be-implemented-by-using-the-having-clause-within-proc-sql/
stats writer. "Can the SAS language’s “SAS: Use HAVING Clause Within PROC SQL” feature be implemented by using the HAVING clause within PROC SQL?." PSYCHOLOGICAL SCALES, 25 Jun. 2024, https://scales.arabpsychology.com/stats/can-the-sas-languages-sas-use-having-clause-within-proc-sql-feature-be-implemented-by-using-the-having-clause-within-proc-sql/.
stats writer. "Can the SAS language’s “SAS: Use HAVING Clause Within PROC SQL” feature be implemented by using the HAVING clause within PROC SQL?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/can-the-sas-languages-sas-use-having-clause-within-proc-sql-feature-be-implemented-by-using-the-having-clause-within-proc-sql/.
stats writer (2024) 'Can the SAS language’s “SAS: Use HAVING Clause Within PROC SQL” feature be implemented by using the HAVING clause within PROC SQL?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/can-the-sas-languages-sas-use-having-clause-within-proc-sql-feature-be-implemented-by-using-the-having-clause-within-proc-sql/.
[1] stats writer, "Can the SAS language’s “SAS: Use HAVING Clause Within PROC SQL” feature be implemented by using the HAVING clause within PROC SQL?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, June, 2024.
stats writer. Can the SAS language’s “SAS: Use HAVING Clause Within PROC SQL” feature be implemented by using the HAVING clause within PROC SQL?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.