What I have is
Value Date_Time Repeat_Flag
20 22JAN18:16:27:29 0
10 22JAN18:16:27:30 0
5 22JAN18:16:27:31 1
20 22JAN18:16:27:33 1
5 22JAN18:16:27:36 0
What I want is
Value Date_Time Repeat_Flag
20 22JAN18:16:27:29 0
10 22JAN18:16:27:30 0
5 22JAN18:16:27:31 1
5 22JAN18:16:27:32 0
20 22JAN18:16:27:33 1
20 22JAN18:16:27:34 0
20 22JAN18:16:27:35 0
5 22JAN18:16:27:36 0
I know that I could use the EST package to do this but I do not have the licence. This means I need to find another way. I already tried several things but nothing is working exactly as I need it to be.
Thanks a lot for your help 🐵
data have;
input Value:best12. Date_Time:datetime16. Repeat_Flag:best12.;
format Date_Time datetime16.;
datalines;
20 22JAN18:16:27:29 0
10 22JAN18:16:27:30 0
5 22JAN18:16:27:31 1
20 22JAN18:16:27:33 1
5 22JAN18:16:27:36 0
;
run;
data want(drop=i j);
set have;
retain i;
if _N_=1 then i=0;
if Repeat_Flag=1 then do;
output;
i=i+1;
do j = 1 to i by 1;
Date_Time=Date_Time+1;
Repeat_Flag=0;
output;
end;
end;
else do;
output;
end;
run;
Please let us know if this worked for you.
Post test data in the form of a datastep!
Also post what you have tried.
At a guess, as no data to work with:
data want; set have; retain lst_data_time; if _n_=1 then lst_date_time=date_time; else do; if date_time ne lst_date_time + 1 then do; old_dt=date_time; old_flag=repeat_flag; do i=lst_date_time+1 to date_time-1; date_time=date_time+i; repeat_flag=0; output; end; date_time=old_dt; repeat_flag=old_flag; output; end; else output; end; run;
Hi RW9, you are right, next time I'll post the test data in a datastep format.
data have;
input Value:best12. Date_Time:datetime16. Repeat_Flag:best12.;
format Date_Time datetime16.;
datalines;
20 22JAN18:16:27:29 0
10 22JAN18:16:27:30 0
5 22JAN18:16:27:31 1
20 22JAN18:16:27:33 1
5 22JAN18:16:27:36 0
;
run;
data want(drop=i j);
set have;
retain i;
if _N_=1 then i=0;
if Repeat_Flag=1 then do;
output;
i=i+1;
do j = 1 to i by 1;
Date_Time=Date_Time+1;
Repeat_Flag=0;
output;
end;
end;
else do;
output;
end;
run;
Please let us know if this worked for you.
Thanks a lot Satish!
I think you can do this just looking at Date_Time, and don't even need to consider Repeat_Flag. I'll keep Repeat_Flag around just to be consistent with what you asked for:
data want;
set have end=done;
output;
if done=0;
_n_ = _n_ + 1;
set have (keep=Date_Time rename=(Date_Time = next_Date_Time)) point=_n_;
if next_Date_Time - Date_Time > 1;
Repeat_Flag = 0;
do Date_Time = Date_Time + 1 to next_Date_Time - 1;
output;
end;
drop next_Date_Time;
run;
data have;
input Value:best12. Date_Time:datetime16. Repeat_Flag:best12.;
format Date_Time datetime16.;
datalines;
20 22JAN18:16:27:29 0
10 22JAN18:16:27:30 0
5 22JAN18:16:27:31 1
20 22JAN18:16:27:33 1
5 22JAN18:16:27:36 0
;
run;
data want;
merge have have(keep=Date_Time rename=(Date_Time=_Date_Time) firstobs=2);
output;
do i=Date_Time+1 to coalesce(_Date_Time-1,0);
Date_Time=i;Repeat_Flag=0;output;
end;
drop i _Date_Time;
run;
proc print noobs;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.