topic Re: Insert one or many copies of a row when a condition is meet while correcting time stamp in SAS Programming

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

EBB — Tue, 27 Feb 2018 10:02:30 GMT

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 🐵

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

RW9 — Tue, 27 Feb 2018 10:10:43 GMT

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;

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

Satish_Parida — Tue, 27 Feb 2018 10:39:19 GMT

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.

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

Astounding — Tue, 27 Feb 2018 11:08:23 GMT

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;

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

Ksharp — Tue, 27 Feb 2018 12:42:12 GMT

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;

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

EBB — Wed, 28 Feb 2018 06:46:58 GMT

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

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

EBB — Wed, 28 Feb 2018 06:47:31 GMT

Thanks a lot Satish!