BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
EBB
Obsidian | Level 7 EBB
Obsidian | Level 7

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 🐵

1 ACCEPTED SOLUTION

Accepted Solutions
Satish_Parida
Lapis Lazuli | Level 10

 

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.

 

View solution in original post

6 REPLIES 6
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Obsidian | Level 7 EBB
Obsidian | Level 7

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

Satish_Parida
Lapis Lazuli | Level 10

 

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.

 

Astounding
PROC Star

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
Super User
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;

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 960 views
  • 3 likes
  • 5 in conversation