choose closest value

[cathy_sas]

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

[LinusH]

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

[RW9]

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;