DATA Step, Macro, Functions and more

Proc sql: Group by and Having

Reply
Frequent Contributor
Posts: 75

Proc sql: Group by and Having

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;
Super User
Posts: 5,256

Re: Proc sql: Group by and Having

You need to use an aggregate function (sum) if you wish to filter on an aggregated value.
Data never sleeps
Super User
Posts: 17,784

Re: Proc sql: Group by and Having

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.

Super User
Posts: 5,256

Re: Proc sql: Group by and Having

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.
Data never sleeps
Ask a Question
Discussion stats
  • 3 replies
  • 484 views
  • 0 likes
  • 3 in conversation