DATA Step, Macro, Functions and more

Select closest observation

Reply
Frequent Contributor
Frequent Contributor
Posts: 101

Select closest observation

I have dataset with multiple observations per subject.

 

Have:

IDtv
1302.95
1225.11
1232.33
1214.3
1282.28

 

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

want:  
IDtv
1232.33

 

Any help to do this in sas?

 

Thanks

 

Super User
Super User
Posts: 7,392

Re: Select closest observation

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.

Frequent Contributor
Frequent Contributor
Posts: 101

Re: Select closest observation

Thanks RW9
Respected Advisor
Posts: 3,887

Re: Select closest observation

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;
Frequent Contributor
Frequent Contributor
Posts: 101

Re: Select closest observation

Thanks Patrick
Super User
Posts: 6,928

Re: Select closest observation

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Frequent Contributor
Frequent Contributor
Posts: 101

Re: Select closest observation

Thanks Kurt
Ask a Question
Discussion stats
  • 6 replies
  • 275 views
  • 0 likes
  • 4 in conversation