topic Re: Proc sql: Group by and Having in SAS Programming

Proc sql: Group by and Having

apple — Sun, 07 Feb 2016 08:10:03 GMT

Using SAS 7.

New to proc sql. I don't quite understand what Proc sql: Group by and Having will give me.

Example: dataset have

Name	Date	Amount Spend
John	Dec-17	15
John	Oct-16	3
Peter	Sep-16	70
Peter	Feb-14	60

Will the below code output John as he has Amount_Spend<10? Or will only John record with Amount Spend=15 be output? Thank you

proc sql;
create table filtered As select * from have
group by name
having Amount_Spend> =10;
quit;

Re: Proc sql: Group by and Having

LinusH — Sun, 07 Feb 2016 13:30:40 GMT

You need to use an aggregate function (sum) if you wish to filter on an aggregated value.

Re: Proc sql: Group by and Having

Reeza — Sun, 07 Feb 2016 13:54:05 GMT

That's not a typical use of the HAVING clause, it's typically used to filter aggregate functions. In your case, it will work as the equivalent of a WHERE clause, so you should get everything except the first row in your results.

Here's an example that you should be able to run, where it picks anyone who has a height greater than the avg height, where avg height is calculated for each sex.

proc sql;
create table want as
select *, mean(height) as avg_height
from sashelp.class
group by sex
having height>avg_height;
quit;

PS. I'm assuming you're using SAS EG 7? SAS 7 isn't a valid software version.

Re: Proc sql: Group by and Having

LinusH — Sun, 07 Feb 2016 14:27:18 GMT

Just some SAS trivia: I think that there were a SAS7, but it was released to a very limited list of US customers who couldn't wait for V8.

Re: Proc sql: Group by and Having

jffeudo86 — Tue, 18 Jun 2019 19:34:59 GMT

Having is used to filter grouped/aggregated numbers. In your example, amount_spend was not aggregated, so it will cause an error.

proc sql;
create table filtered As select name,sum(amount_spend) as Spent from have
group by name
having sum(Amount_Spend)> =10;
quit;

This code will have John->18 and Peter->130.

However:
proc sql;
create table filtered As select name,sum(amount_spend) as Spent from have
where amount_spend>=10
group by name
having sum(Amount_Spend)> =10;
quit;

will give you John->15 and Peter->130. John's amount_spend = 3 was filtered out by the where clause.

Re: Proc sql: Group by and Having

Reeza — Tue, 18 Jun 2019 19:53:01 GMT

It probably isn't super useful to respond to 3+ year old threads 😉