topic Re: How to retain values in a SAS dataset with datetime? in SAS Programming

How to retain values in a SAS dataset with datetime?

MM88 — Wed, 17 Aug 2022 14:57:44 GMT

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

Re: How to retain values in a SAS dataset with datetime?

PeterClemmensen — Wed, 17 Aug 2022 17:08:55 GMT

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

Re: How to retain values in a SAS dataset with datetime?

PaigeMiller — Wed, 17 Aug 2022 17:15:07 GMT

I did not know that this was a feature of PROC TIMESERIES. Thanks!

Re: How to retain values in a SAS dataset with datetime?

PeterClemmensen — Wed, 17 Aug 2022 18:15:49 GMT

Yeah, Proc Timeseries is quite cool for tasks like this 🙂

Re: How to retain values in a SAS dataset with datetime?

Tom — Wed, 17 Aug 2022 18:57:04 GMT

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

Re: How to retain values in a SAS dataset with datetime?

MM88 — Thu, 18 Aug 2022 06:31:30 GMT

Hi @PeterClemmensen,
Thanks for your solution. Unfortunately I don´t have SAS/ETS.
Regards

Re: How to retain values in a SAS dataset with datetime?

Ksharp — Thu, 18 Aug 2022 12:27:47 GMT

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;