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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: