This would be more manageable if you had the DATE of start_ts (which is a datetime value). Then it is more trivial to propagate value to the beginning or end of a date. So I made a VIEW of dataset1 and dataset2, with a new DATE variable.
Building NEED2, the view derived from DATASET2, each observation is not only copied, but all "holes" are also output, with appropriate START_DS and END_DS, and VALUE set to missing. "Filling holes" means not only between observations, but also start-of-day and end-of-day time spans not covered in the dataset2 observation.
data dataset1;
input OBS ID $ START_TS :datetime. END_TS :datetime. Value;
format start_ts end_ts datetime20.;
datalines;
1 A 01JAN2025:00:00:00 02JAN2025:00:00:00 10
2 A 02JAN2025:00:00:00 03JAN2025:00:00:00 20
3 A 03JAN2025:00:00:00 04JAN2025:00:00:00 10
4 A 04JAN2025:00:00:00 05JAN2025:00:00:00 20
5 A 05JAN2025:00:00:00 06JAN2025:00:00:00 10
6 A 06JAN2025:00:00:00 07JAN2025:00:00:00 20
run;
data dataset2;
input OBS ID $ START_TS :datetime. END_TS :datetime. Value;
format START_TS END_TS datetime20.;
datalines;
1 A 01JAN2025:00:00:00 01JAN2025:10:00:00 99
2 A 02JAN2025:08:00:00 02JAN2025:11:00:00 99
3 A 03JAN2025:22:00:00 04JAN2025:02:00:00 99
4 A 06JAN2025:03:00:00 06JAN2025:05:00:00 99
5 A 06JAN2025:23:00:00 07JAN2025:00:00:00 99
run;
data need2 (drop=_: obs) /view=need2 ;
set dataset2 ;
by ID;
/* When starting new day or new ID, check if dummy starting record is needed */
if first.ID=1 or dif(datepart(start_ts))>0 then do;
if timepart(start_ts) ^= '00:00:00't then do; /* Yes, a Dummy rec is needed*/
end_ts=start_ts;
start_ts=intnx('dtday',start_ts,0,'begin');
date=datepart(start_ts);
_value=value;
call missing(value);
output;
value=_value;
end;
end;
merge dataset2 /*Reread the same obs, to recover VALUE*/
dataset2 (firstobs=2 keep=id start_ts rename=(id=_nxt_id start_ts=_nxt_st)) ;
_orig_end=end_ts;
do until (start_ts=_orig_end);
date=datepart(start_ts);
end_ts=min(_orig_end,intnx('dtday',start_ts,1,'begin'));
output;
start_ts=end_ts;
end;
/* Most obs will need a trailing dummy record */
if timepart(end_ts)^='00:00:00't then do;
start_ts=end_ts;
end_ts=_nxt_st;
if last.ID or datepart(_nxt_st)^=datepart(start_ts) then end_ts=intnx('dtday',start_ts,1,'begin');
call missing(value);
date=datepart(start_ts);
output;
end;
format date date9. ;
run;
data need1 (drop=obs)/view=need1;
set dataset1;
date=datepart(start_ts);
format date date9. ;
run;
data want (drop=_:);
set need1 (in=in1) need2 (in=in2);
by ID date;
retain _value;
if first.date=1 and last.date=1 then output;
else if in1 then _value=value;
else if in2 then do;
value=coalesce(value,_value);
output;
end;
run;
... View more
