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;
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.
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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.