Insert one or many copies of a row when a condition is meet while correcting time stamp

[EBB]

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 🐵

[RW9]

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;
        

[EBB]

Hi RW9, you are right, next time I'll post the test data in a datastep format. 

[Satish_Parida · Accepted solution]

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.

[EBB]

Thanks a lot Satish!

[Astounding]

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;

[Ksharp]

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;