Hi experts,
I need help retaining values in a SAS dataset and completing the column datetime (to the level of seconds) when not existing.
My dataset looks like:
data HAVE;
input type$ DATE:datetime18. value;
format date datetime18.;
cards;
A 19JUN01:21:06:55 534
A 19JUN01:21:06:58 590
A 19JUN01:21:07:02 600
A 19JUN01:21:07:04 602
B 18JUN01:22:06:58 105
B 18JUN01:22:07:03 110
;
run;
I need to fill the missing datetime and repeat the value when needed.
My result dataset should be:
data WANT;
input type$ DATE:datetime18. value;
format date datetime18.;
cards;
A 19JUN01:21:06:55 534
A 19JUN01:21:06:56 534
A 19JUN01:21:06:57 534
A 19JUN01:21:06:58 590
A 19JUN01:21:06:59 590
A 19JUN01:21:07:00 590
A 19JUN01:21:07:01 590
A 19JUN01:21:07:02 600
A 19JUN01:21:07:03 600
A 19JUN01:21:07:04 602
B 18JUN01:22:06:58 105
B 18JUN01:22:06:59 105
B 18JUN01:22:07:00 105
B 18JUN01:22:07:01 105
B 18JUN01:22:07:02 105
B 18JUN01:22:07:03 110
;
run;
Thanks for your suggestions. Regards
You don't have ETS licensed then just use a data step. The tricky part is find the value of the next "date" (actually in this case the next datetime).
data HAVE;
input type $ DATE :datetime. value;
format date datetime19.;
cards;
A 19JUN01:21:06:55 534
A 19JUN01:21:06:58 590
A 19JUN01:21:07:02 600
A 19JUN01:21:07:04 602
B 18JUN01:22:06:58 105
B 18JUN01:22:07:03 110
;
data want;
set have;
by type date;
set have(firstobs=2 keep=date rename=(date=next_date)) have(obs=1 drop=_all_);
if last.type then next_date=.;
else next_date=next_date-1;
do date=date to coalesce(next_date,date);
output;
end;
drop next_date;
run;
Result
data HAVE;
input type$ DATE:datetime18. value;
format date datetime18.;
cards;
A 19JUN01:21:06:55 534
A 19JUN01:21:06:58 590
A 19JUN01:21:07:02 600
A 19JUN01:21:07:04 602
B 18JUN01:22:06:58 105
B 18JUN01:22:07:03 110
;
run;
proc timeseries data=have out=want;
by type;
id date interval = second
setmiss = prev;
var value;
run;
Result:
type DATE value A 19JUN01:21:06:55 534 A 19JUN01:21:06:56 534 A 19JUN01:21:06:57 534 A 19JUN01:21:06:58 590 A 19JUN01:21:06:59 590 A 19JUN01:21:07:00 590 A 19JUN01:21:07:01 590 A 19JUN01:21:07:02 600 A 19JUN01:21:07:03 600 A 19JUN01:21:07:04 602 B 18JUN01:22:06:58 105 B 18JUN01:22:06:59 105 B 18JUN01:22:07:00 105 B 18JUN01:22:07:01 105 B 18JUN01:22:07:02 105 B 18JUN01:22:07:03 110
I did not know that this was a feature of PROC TIMESERIES. Thanks!
Yeah, Proc Timeseries is quite cool for tasks like this 🙂
You don't have ETS licensed then just use a data step. The tricky part is find the value of the next "date" (actually in this case the next datetime).
data HAVE;
input type $ DATE :datetime. value;
format date datetime19.;
cards;
A 19JUN01:21:06:55 534
A 19JUN01:21:06:58 590
A 19JUN01:21:07:02 600
A 19JUN01:21:07:04 602
B 18JUN01:22:06:58 105
B 18JUN01:22:07:03 110
;
data want;
set have;
by type date;
set have(firstobs=2 keep=date rename=(date=next_date)) have(obs=1 drop=_all_);
if last.type then next_date=.;
else next_date=next_date-1;
do date=date to coalesce(next_date,date);
output;
end;
drop next_date;
run;
Result
data HAVE;
input type $ DATE :datetime. value;
format date datetime19.;
cards;
A 19JUN01:21:06:55 534
A 19JUN01:21:06:58 590
A 19JUN01:21:07:02 600
A 19JUN01:21:07:04 602
B 18JUN01:22:06:58 105
B 18JUN01:22:07:03 110
;
data want;
merge have have(keep=type date rename=(type=_type date=_date) firstobs=2);
output;
if type=_type then do;
do date=date+1 to _date-1;
output;
end;
end;
drop _type _date;
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.