topic Re: pick the minimum and keep only that value (proc sql) in New SAS User

pick the minimum and keep only that value (proc sql)

sasprogramming — Mon, 09 Dec 2019 05:22:36 GMT

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!

Re: pick the minimum and keep only that value (proc sql)

Jagadishkatam — Mon, 09 Dec 2019 05:57:26 GMT

Please try

proc sql;
create table want as select id,date,min(points) as points
from have group by id,date;
quit;

Re: pick the minimum and keep only that value (proc sql)

Patrick — Mon, 09 Dec 2019 06:08:32 GMT

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;

@Jagadishkatam

I believe the SQL you've posted won't return the desired result.

Re: pick the minimum and keep only that value (proc sql)

Jagadishkatam — Mon, 09 Dec 2019 06:11:04 GMT

@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;

Re: pick the minimum and keep only that value (proc sql)

Patrick — Mon, 09 Dec 2019 06:18:26 GMT

@Jagadishkatam

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;

Re: pick the minimum and keep only that value (proc sql)

Jagadishkatam — Mon, 09 Dec 2019 06:22:04 GMT

I agree with you @Patrick

Re: pick the minimum and keep only that value (proc sql)

Astounding — Tue, 10 Dec 2019 07:51:40 GMT

Why force SQL to do the job, when SAS has already created a tool for this purpose?

proc summary data=have nway;
var points;
class id date;
output out=want (drop=_type_ _freq_) min= ;
run;