02-07-2016 03:10 AM
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
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;
02-07-2016 08:54 AM
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.