Returning Earliest date by multiple Keys

Krueger1 — Fri, 19 Nov 2021 21:26:24 GMT

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;

Re: Returning Earliest date by multiple Keys

PeterClemmensen — Fri, 19 Nov 2021 21:50:32 GMT

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;

Re: Returning Earliest date by multiple Keys

Krueger1 — Fri, 19 Nov 2021 21:51:57 GMT

Retain is what I was forgetting about thank you I knew I was over complicating this!

topic Returning Earliest date by multiple Keys in SAS Programming

Returning Earliest date by multiple Keys

Re: Returning Earliest date by multiple Keys

Re: Returning Earliest date by multiple Keys