topic Re: Want to get previous date value to next record which is less than current record date in SAS Programming

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

SAS_Learner2 — Tue, 18 Apr 2023 13:54:28 GMT

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.

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

ballardw — Tue, 18 Apr 2023 14:33:46 GMT

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.

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

Tom — Tue, 18 Apr 2023 14:39:18 GMT

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.

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

A_Kh — Tue, 18 Apr 2023 14:52:27 GMT

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;

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

Tom — Tue, 18 Apr 2023 15:05:47 GMT

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;