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.
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;
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.
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.
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;
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;
Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!
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.
Ready to level-up your skills? Choose your own adventure.