DATA Step, Macro, Functions and more

Time Interval (in days) across rows

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 8
Accepted Solution

Time Interval (in days) across rows

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!


Accepted Solutions
Solution
‎07-07-2017 11:28 AM
Occasional Contributor
Posts: 8

Re: Time Interval (in days) across rows

Posted in reply to Jagadishkatam

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


All Replies
Super User
Posts: 19,789

Re: Time Interval (in days) across rows

DIF()

Trusted Advisor
Posts: 1,137

Re: Time Interval (in days) across rows

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
Solution
‎07-07-2017 11:28 AM
Occasional Contributor
Posts: 8

Re: Time Interval (in days) across rows

Posted in reply to Jagadishkatam

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!

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 137 views
  • 2 likes
  • 3 in conversation