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;
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
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
Thanks. This worked!!
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
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.
Ready to level-up your skills? Choose your own adventure.