22.9 C
London
Monday, July 21, 2025
HomeSASData Munging in SASSAS: How to Use HAVING Clause Within PROC SQL

SAS: How to Use HAVING Clause Within PROC SQL

Related stories

Learn About Opening an Automobile Repair Shop in India

Starting a car repair shop is quite a good...

Unlocking the Power: Embracing the Benefits of Tax-Free Investing

  Unlocking the Power: Embracing the Benefits of Tax-Free Investing For...

Income Splitting in Canada for 2023

  Income Splitting in Canada for 2023 The federal government’s expanded...

Can I Deduct Home Office Expenses on my Tax Return 2023?

Can I Deduct Home Office Expenses on my Tax...

Canadian Tax – Personal Tax Deadline 2022

  Canadian Tax – Personal Tax Deadline 2022 Resources and Tools...

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

Subscribe

- Never miss a story with notifications

- Gain full access to our premium content

- Browse free from up to 5 devices at once

Latest stories