BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
SAS_Learner2
Calcite | Level 5

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.

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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;

 

View solution in original post

4 REPLIES 4
ballardw
Super User

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
Super User Tom
Super User

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
Lapis Lazuli | Level 10

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
Super User Tom
Super User

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;

 

sas-innovate-2024.png

 

Time is running out to save with the early bird rate. Register by Friday, March 1 for just $695 - $100 off the standard rate.

 

Check out the agenda and get ready for a jam-packed event featuring workshops, super demos, breakout sessions, roundtables, inspiring keynotes and incredible networking events. 

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 416 views
  • 1 like
  • 4 in conversation