topic choose closest value in SAS Programming

choose closest value

cathy_sas — Mon, 08 Aug 2016 05:41:31 GMT

Hi All,

I have a 2 datasets, one dataset have start date & end date , value and duration. And another dataset have dt(date for that record). i need to dervie a variable value_dur based on

if stdt=dt^=endt then derive value_dur= last value/dur

else if (stdt<adt<=endt) or (stdt=adt=exdt) then derive value_dur= value/dur

if endt is not overlap with next stdt other wise choose highest value/dur

have1:

id stdt endt value dur

100 23jul2012 19aug2012 21 1

100 20aug2012 17sep2012 26 .5

100 18sep2012 14oct2012 70 1.5

100 15oct2012 11nov2012 30 1.6

100 12nov2012 28nov2012 50 1.4

have2:

id dt

100 23jul2012

100 30jul2012

100 13aug2012

100 27aug2012

100 10sep2012

100 24sep2012

100 05oct2012

100 22oct2012

100 02nov2012

100 19nov2012

100 03dec2012

100 07dec2012

want:

id dt value_dur

100 23jul2012 21

100 30jul2012 21

100 13aug2012 21

100 27aug2012 52

100 10sep2012 52

100 24sep2012 46.66

100 05oct2012 46.66

100 22oct2012 18.75

100 02nov2012 18.75

100 19nov2012 35.71

100 03dec2012 .

100 07dec2012 .

I am trying to merge the have1 & hav2

proc sql;

create table temp as

select a.*,b.stdt,b.endt,b.value,b.dur

from have2 as a left join have1 as b

on a.id=b.id & b.stdt<=a.dt

order by a.id,a.dt,b.stdt;

quit;

Thanks

Cathy

Re: choose closest value

LinusH — Mon, 08 Aug 2016 05:33:59 GMT

Please show a "want" data set to further explain your requirement.

Re: choose closest value

RW9 — Mon, 08 Aug 2016 08:20:47 GMT

Firstly, please post test data in the form of a datastep, and please use good code formatting so your code is easy to read (you can use the code insertion {i} above the box to keep formatting). For your problem I would do it in two steps - first merge on the data, then step through that retaining the last value:

data have1;
  informat stdt endt date9.;
  input id stdt endt value dur;
datalines;
100 23jul2012 19aug2012 21 1
100 20aug2012 17sep2012 26 .5
;
run;

data have2;
  informat dt date9.;
  input id dt; 
datalines; 
100 23jul2012
100 30jul2012
100 13aug2012
100 27aug2012
100 10sep2012
;
run;

proc sql;
  create table WANT as
  select  A.*,
          B.STDT,
          B.ENDT,
          B.VALUE,
          B.DUR
  from HAVE2 as A 
  left join HAVE1 as B
  on      A.ID=B.ID 
  and     A.DT between B.STDT and B.ENDT
  order by  A.ID,
            A.DT,
            B.STDT;
quit;

data want;
  set want;
  retain lstv;
  by id;
  if first.id then lstv=value;
  if stdt=endt then result=value/lstv;
  else result=value/dur;
run;