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 open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
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.