I have the current dataset in SAS:
ID | Date | Points |
1 | 1/01/2019 | 0 |
2 | 1/01/2019 | -200 |
3 | 1/01/2019 | 0 |
3 | 1/01/2019 | 0 |
3 | 1/01/2019 | 0 |
3 | 1/01/2019 | 0 |
3 | 1/01/2019 | 0 |
3 | 1/01/2019 | 0 |
3 | 1/01/2019 | 0 |
3 | 1/01/2019 | -100 |
3 | 1/01/2019 | -100 |
3 | 1/01/2019 | -100 |
3 | 1/01/2019 | -100 |
3 | 1/01/2019 | -100 |
3 | 1/01/2019 | -100 |
3 | 1/01/2019 | -100 |
3 | 1/01/2019 | -300 |
3 | 1/01/2019 | -100 |
3 | 1/01/2019 | -100 |
3 | 1/01/2019 | -100 |
4 | 1/01/2019 | -200 |
5 | 1/01/2019 | 0 |
5 | 1/01/2019 | -200 |
5 | 1/01/2019 | -100 |
I want to create a new dataset with only one entry for each ID, and for this ID I want to choose the minimum value for points for each ID. My desired result looks like this:
ID | Date | Points |
1 | 1/01/2019 | 0 |
2 | 1/01/2019 | -200 |
3 | 1/01/2019 | -300 |
3 | 1/01/2019 | 0 |
4 | 1/01/2019 | -200 |
5 | 1/01/2019 | -200 |
Is there a way to using proc sql in SAS?
Thanks!
Please try
proc sql;
create table want as select id,date,min(points) as points
from have group by id,date;
quit;
Below two options (code not tested).
/* option 1 */
proc sql;
create table want as
select id,date,points
from have
group by id
having min(points)=points
;
quit;
/* option 2 */
proc sort data=have;
by id points;
run;
data want;
set have;
by id;
if first.id;
run;
I believe the SQL you've posted won't return the desired result.
@Patrick , seems like the below code is working I tested the same per the data, I believe for the record id 3 we should have only one row of -300 as it is the minimum values under ID 3
data have;
input ID Date:ddmmyy10. Points;
cards;
1 1/01/2019 0
2 1/01/2019 -200
3 1/01/2019 0
3 1/01/2019 0
3 1/01/2019 0
3 1/01/2019 0
3 1/01/2019 0
3 1/01/2019 0
3 1/01/2019 0
3 1/01/2019 -100
3 1/01/2019 -100
3 1/01/2019 -100
3 1/01/2019 -100
3 1/01/2019 -100
3 1/01/2019 -100
3 1/01/2019 -100
3 1/01/2019 -300
3 1/01/2019 -100
3 1/01/2019 -100
3 1/01/2019 -100
4 1/01/2019 -200
5 1/01/2019 0
5 1/01/2019 -200
5 1/01/2019 -100
;
proc sql;
create table want as select id,date,min(points) as points
from have group by id,date;
quit;
The issue with your code doesn't surface with the sample data provided. Run with below and you'll see what I'm talking about.
data have;
input ID Date:ddmmyy10. Points;
format date date9.;
cards;
3 1/01/2019 0
3 2/01/2019 -300
3 1/01/2019 -100
3 3/01/2019 -200
;
proc sql;
/* create table want as */
select id,date,min(points) as points
from have
group by id,date
;
quit;
proc sql;
/* create table want as */
select id,date,points
from have
group by id
having min(points)=points
;
quit;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.