Hello,
It's been quite some time since I've meddled with SAS but am really struggling to understand the logic here. I feel like I'm overcomplicating it. Below is what I have, want and what I've attempted (excluding the multiple variations).
Simply put. I want it to know is an ID's earliest revoke (revoke = 1) then that should be the date that is shown. If at anytime revoke swaps to 0 and back to 1 then I want this new date to show.
data have;
input ID Revoke date :datetime.;
format date datetime20.;
datalines;
1 0 01JAN2020:16:49:00
1 1 05JAN2020:16:49:00
1 1 10JAN2020:16:49:00
1 1 20JAN2020:16:49:00
2 0 01JAN2020:16:49:00
2 1 02JAN2020:16:49:00
2 0 23JUL2020:16:49:00
2 1 25JUL2020:16:49:00
2 1 02AUG2020:16:49:00
3 0 01JAN2020:16:49:00
3 0 03JUL2020:16:49:00
3 1 13JUL2020:16:49:00
3 1 23JUL2020:16:49:00
3 0 24JUL2020:16:49:00
3 1 25JUL2020:16:49:00
;
data want;
input ID Revoke date :datetime. NewDate :datetime.;
format date NewDate datetime20.;
datalines;
1 0 01JAN2020:16:49:00 .
1 1 05JAN2020:16:49:00 05JAN2020:16:49:00
1 1 10JAN2020:16:49:00 05JAN2020:16:49:00
1 1 20JAN2020:16:49:00 05JAN2020:16:49:00
2 0 01JAN2020:16:49:00 .
2 1 02JAN2020:16:49:00 02JAN2020:16:49:00
2 0 23JUL2020:16:49:00 .
2 1 25JUL2020:16:49:00 25JUL2020:16:49:00
2 1 02AUG2020:16:49:00 25JUL2020:16:49:00
3 0 01JAN2020:16:49:00 .
3 0 03JUL2020:16:49:00 .
3 1 13JUL2020:16:49:00 13JUL2020:16:49:00
3 1 23JUL2020:16:49:00 13JUL2020:16:49:00
3 0 24JUL2020:16:49:00 .
3 1 25JUL2020:16:49:00 25JUL2020:16:49:00
;
data testwant;
set have;
format newdate datetime20.;
by id date revoke;
if first.revoke then newDate = date;
if revoke = 0 then newdate = .;
if revoke = lag(revoke) and id = lag(id) then newDate = lag(newdate);
run;
How about
data have;
input ID Revoke date :datetime.;
format date datetime20.;
datalines;
1 0 01JAN2020:16:49:00
1 1 05JAN2020:16:49:00
1 1 10JAN2020:16:49:00
1 1 20JAN2020:16:49:00
2 0 01JAN2020:16:49:00
2 1 02JAN2020:16:49:00
2 0 23JUL2020:16:49:00
2 1 25JUL2020:16:49:00
2 1 02AUG2020:16:49:00
3 0 01JAN2020:16:49:00
3 0 03JUL2020:16:49:00
3 1 13JUL2020:16:49:00
3 1 23JUL2020:16:49:00
3 0 24JUL2020:16:49:00
3 1 25JUL2020:16:49:00
;
data want;
set have;
by ID Revoke notsorted;
if Revoke = 0 then NewDate = .;
if Revoke = 1 and first.Revoke then NewDate = Date;
retain NewDate;
format NewDate datetime20.;
run;
How about
data have;
input ID Revoke date :datetime.;
format date datetime20.;
datalines;
1 0 01JAN2020:16:49:00
1 1 05JAN2020:16:49:00
1 1 10JAN2020:16:49:00
1 1 20JAN2020:16:49:00
2 0 01JAN2020:16:49:00
2 1 02JAN2020:16:49:00
2 0 23JUL2020:16:49:00
2 1 25JUL2020:16:49:00
2 1 02AUG2020:16:49:00
3 0 01JAN2020:16:49:00
3 0 03JUL2020:16:49:00
3 1 13JUL2020:16:49:00
3 1 23JUL2020:16:49:00
3 0 24JUL2020:16:49:00
3 1 25JUL2020:16:49:00
;
data want;
set have;
by ID Revoke notsorted;
if Revoke = 0 then NewDate = .;
if Revoke = 1 and first.Revoke then NewDate = Date;
retain NewDate;
format NewDate datetime20.;
run;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.