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
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.