BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Kaos
Fluorite | Level 6

Hello Experts!

 

I need to calculate the time difference across rows for the data below and assign it to a third column.

 

Data -

 

Col1    Col2                

abc     2016/05/23

abc     2016/08/13

abc     2016/11/29

abc     2017/02/15

 

Is there a function I can use to calculate the time interval in days between each record?

 

Thanks in advance!

1 ACCEPTED SOLUTION

Accepted Solutions
Kaos
Fluorite | Level 6

Thank you for your suggestion. I used a combination of lag and dif functions to get the answer I was looking for.

 

My dataset looked like this -

 

Col1    Col2                

abc     2016/05/23

abc     2016/08/13

abc     2016/11/29

abc     2017/02/15

pqr     2016/04/16

pqr     2017/01/27

 

The code I used is -

 

Data File2;

Set File1;

By col1;

Lag_col2 = lag(col2);

Dif_col2 = dif(col2);

If first.col1 then do;

  lag_col2 = .;

  dif_col2 = .;

End;

Run;

 

 

The final answerset looked like this -

 

Col1    Col2                  Lag_col2           Dif_col2

abc     2016/05/23        .                         .

abc     2016/08/13        2016/05/23        82

abc     2016/11/29        2016/08/13        108

abc     2017/02/15        2016/11/29        78

pqr     2016/04/16         .                        .

pqr     2017/01/27         2016/04/16       286

 

The column Dif_Col2 gives me the difference between the dates for each id on col1.

 

Thanks again!

View solution in original post

3 REPLIES 3
Jagadishkatam
Amethyst | Level 16

we have lag and intck function to achieve the expected output

 

option missing=0;
data have;
input col1 $          col2 :yymmdd10.;
lagdate=intck('day',lag(col2),col2);
format col2 date9.;
cards;          
abc     2016/05/23
abc     2016/08/13
abc     2016/11/29
abc     2017/02/15
;


Thanks,
Jag
Kaos
Fluorite | Level 6

Thank you for your suggestion. I used a combination of lag and dif functions to get the answer I was looking for.

 

My dataset looked like this -

 

Col1    Col2                

abc     2016/05/23

abc     2016/08/13

abc     2016/11/29

abc     2017/02/15

pqr     2016/04/16

pqr     2017/01/27

 

The code I used is -

 

Data File2;

Set File1;

By col1;

Lag_col2 = lag(col2);

Dif_col2 = dif(col2);

If first.col1 then do;

  lag_col2 = .;

  dif_col2 = .;

End;

Run;

 

 

The final answerset looked like this -

 

Col1    Col2                  Lag_col2           Dif_col2

abc     2016/05/23        .                         .

abc     2016/08/13        2016/05/23        82

abc     2016/11/29        2016/08/13        108

abc     2017/02/15        2016/11/29        78

pqr     2016/04/16         .                        .

pqr     2017/01/27         2016/04/16       286

 

The column Dif_Col2 gives me the difference between the dates for each id on col1.

 

Thanks again!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 3 replies
  • 816 views
  • 2 likes
  • 3 in conversation