Want to get previous date value to next record which is less than current record date

[SAS_Learner2]

Hello,

I have data like below. I am strugging to get the desired output.

ID Date1                          Date2
1 18AUG2022:07:26:00 18AUG2022:16:27:00
1 18AUG2022:07:26:00 19AUG2022:15:00:00
1 19AUG2022:02:30:00 19AUG2022:15:00:00
1 19AUG2022:02:30:00 20AUG2022:11:00:00
1 20AUG2022:03:00:00 20AUG2022:11:00:00
1 20AUG2022:03:00:00 21AUG2022:12:30:00
1 21AUG2022:01:00:00 21AUG2022:12:30:00
1 21AUG2022:01:00:00 22AUG2022:11:30:00
1 22AUG2022:02:30:00 22AUG2022:11:30:00
1 22AUG2022:02:30:00 23AUG2022:12:30:00

I want date3 variable to be created and this value should come previous Date2 records and Date3 value should be less than Date1 value.

ID Date1                         Date2                          Date3
1 18AUG2022:07:26:00 18AUG2022:16:27:00 .
1 19AUG2022:02:30:00 19AUG2022:15:00:00 18AUG2022:16:27:00
1 20AUG2022:03:00:00 20AUG2022:11:00:00 19AUG2022:15:00:00
1 21AUG2022:01:00:00 21AUG2022:12:30:00 20AUG2022:11:00:00
1 22AUG2022:02:30:00 22AUG2022:11:30:00 21AUG2022:12:30:00

Please help.

[ballardw]

Do you have more than one ID value in the data? If so, when the ID changes what should happen with date3? Your current description would mean that the date might be from a previous ID.

Please look very closely at your "want" data and then describe what happened to about half the records. You are doing more then getting a previous date value added to a current observation as the "want" shows entire records removed.

So please describe in a bit more detail what the rules involved are. I have highlighted below observations that are apparently removed.

---

@SAS_Learner2 wrote:

Hello,

I have data like below. I am strugging to get the desired output.

ID Date1                          Date2
1 18AUG2022:07:26:00 18AUG2022:16:27:00
1 18AUG2022:07:26:00 19AUG2022:15:00:00
1 19AUG2022:02:30:00 19AUG2022:15:00:00
1 19AUG2022:02:30:00 20AUG2022:11:00:00
1 20AUG2022:03:00:00 20AUG2022:11:00:00
1 20AUG2022:03:00:00 21AUG2022:12:30:00
1 21AUG2022:01:00:00 21AUG2022:12:30:00
1 21AUG2022:01:00:00 22AUG2022:11:30:00
1 22AUG2022:02:30:00 22AUG2022:11:30:00
1 22AUG2022:02:30:00 23AUG2022:12:30:00

I want date3 variable to be created and this value should come previous Date2 records and Date3 value should be less than Date1 value.

ID Date1                         Date2                          Date3
1 18AUG2022:07:26:00 18AUG2022:16:27:00 .
1 19AUG2022:02:30:00 19AUG2022:15:00:00 18AUG2022:16:27:00
1 20AUG2022:03:00:00 20AUG2022:11:00:00 19AUG2022:15:00:00
1 21AUG2022:01:00:00 21AUG2022:12:30:00 20AUG2022:11:00:00
1 22AUG2022:02:30:00 22AUG2022:11:30:00 21AUG2022:12:30:00

Please help.

---

[Tom]

You need to explain the rule that is eliminating some of the observations.

If DATETIME3 is just the lagged value of DATETIME2 then it is NEVER going to be less than DATETIME1.

PS Your dataset does not have any DATE values.  Only DATETIME values.

[A_Kh]

I don't know what is the logic behind this problem, but this code can give you the desired output as you described above:

data want;
	set have;
	by id date1;
	if first.date1 then date3= lag(date2);
	format date3 datetime18.;
	if first.date1;
proc print;run; 
 

[Tom · Accepted solution]

Thank generates the output expected.

I would move the subsetting IF to the first statement. It makes the coding easier.

data have;
  input id (datetime1-datetime2) (:datetime.);
  format datetime: datetime19.;
cards;
1 18AUG2022:07:26:00 18AUG2022:16:27:00
1 18AUG2022:07:26:00 19AUG2022:15:00:00
1 19AUG2022:02:30:00 19AUG2022:15:00:00
1 19AUG2022:02:30:00 20AUG2022:11:00:00
1 20AUG2022:03:00:00 20AUG2022:11:00:00
1 20AUG2022:03:00:00 21AUG2022:12:30:00
1 21AUG2022:01:00:00 21AUG2022:12:30:00
1 21AUG2022:01:00:00 22AUG2022:11:30:00
1 22AUG2022:02:30:00 22AUG2022:11:30:00
1 22AUG2022:02:30:00 23AUG2022:12:30:00
;

data expect ;
  input id (datetime1-datetime3) (:datetime.);
  format datetime: datetime19.;
cards;
1 18AUG2022:07:26:00 18AUG2022:16:27:00 .
1 19AUG2022:02:30:00 19AUG2022:15:00:00 18AUG2022:16:27:00
1 20AUG2022:03:00:00 20AUG2022:11:00:00 19AUG2022:15:00:00
1 21AUG2022:01:00:00 21AUG2022:12:30:00 20AUG2022:11:00:00
1 22AUG2022:02:30:00 22AUG2022:11:30:00 21AUG2022:12:30:00
;

data want;
  set have;
  by id datetime1;
  if first.datetime1;
  datetime3 = lag(datetime2);
  if first.id then datetime3=.;
  format datetime3 datetime19.;
run;

proc compare data=expect compare=want;
run;