topic Re: Select closest observation in SAS Programming

Select closest observation

ari — Wed, 28 Sep 2016 10:10:40 GMT

I have dataset with multiple observations per subject.

Have:

ID	t	v
1	30	2.95
1	22	5.11
1	23	2.33
1	21	4.3
1	28	2.28

I want to create a dataset to keep only one record per subject where the variable 't' closest to value 24.

want:
ID	t	v
1	23	2.33

Any help to do this in sas?

Thanks

Re: Select closest observation

RW9 — Wed, 28 Sep 2016 10:33:57 GMT

Simplest way I can think of is to create an offset var, then sort by offset and take n=1:

data want;
  set have;
  offset=ifn(t<24,24-t,t-24);
run;
proc sort data=want;
  by offset;
run;
data want;
  set want (obs=1);
run;

Not tested as not typing in test data - post as a datastep next time.

Re: Select closest observation

Patrick — Wed, 28 Sep 2016 10:57:00 GMT

or this way:

data have;
input ID t v;
datalines;
1 30 2.95
1 22 5.11
1 23 2.33
1 21 4.3
1 28 2.28
;
run;

proc sql;
  create table want as
    select id, t, v 
    from have
    group by id
    having min(abs(24-t))=abs(24-t)
    ;
quit;

Re: Select closest observation

Kurt_Bremser — Wed, 28 Sep 2016 11:22:03 GMT

Since you indicate there are multiple IDs:

data int;
set have;
offset = abs(t-24);
run;

proc sort data=int;
by id offset;
run;

data want;
set int;
by id;
if first.id;
drop offset;
run;

For larger datasets, this might be more performant than doing it with SQL.

Re: Select closest observation

ari — Wed, 28 Sep 2016 13:35:42 GMT

Thanks RW9

Re: Select closest observation

ari — Wed, 28 Sep 2016 13:35:59 GMT

Thanks Patrick

Re: Select closest observation

ari — Wed, 28 Sep 2016 13:36:18 GMT

Thanks Kurt