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;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.