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

I have the following dataset

 

id time_start time_stop
1 12/4/2019 11:21 12/4/2019 21:12
1 12/4/2019 11:21

12/4/2019 23:59

 

 

What I want to do is for ANY two same time_start, I would like to assign previous time_stop but keep rest the same.

 

 

something like this:

 

id time_one time_two
1 12/4/2019 11:21 12/4/2019 21:12
1 12/4/2019 21:12 12/4/2019 23:59

 

data work.test2;

      merge work.test

work.test(keep=id time_start       

firstobs=2

                                rename=(id=_id time_start=_start_date));

 

      format new_stop_date datetime20.;

      if id=_id and _start_date < time_stop

then new_stop_date=_start_date;

      else new_stop_date=time_stop;

 

      rename time_start=new_start_date;

      drop time_stop _:;

run;

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

Something like this:

data have;
input id time_start :e8601dt16. time_stop :e8601dt16.;
format time_start time_stop e8601dt16.;
datalines;
1 2019-04-12T11:21 2019-04-12T21:12
1 2019-04-12T11:21 2019-04-12T23:59
;

data want;
set have;
by id;
lts = lag(time_stop);
if not first.id and lts > time_start then time_start = lts;
drop lts;
run;

proc print data=want noobs;
run;

Result:

id    time_start          time_stop

 1    2019-04-12T11:21    2019-04-12T21:12
 1    2019-04-12T21:12    2019-04-12T23:59

View solution in original post

2 REPLIES 2
Kurt_Bremser
Super User

Something like this:

data have;
input id time_start :e8601dt16. time_stop :e8601dt16.;
format time_start time_stop e8601dt16.;
datalines;
1 2019-04-12T11:21 2019-04-12T21:12
1 2019-04-12T11:21 2019-04-12T23:59
;

data want;
set have;
by id;
lts = lag(time_stop);
if not first.id and lts > time_start then time_start = lts;
drop lts;
run;

proc print data=want noobs;
run;

Result:

id    time_start          time_stop

 1    2019-04-12T11:21    2019-04-12T21:12
 1    2019-04-12T21:12    2019-04-12T23:59
monday89
Fluorite | Level 6

Thanks. This worked!!

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 2 replies
  • 720 views
  • 1 like
  • 2 in conversation