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!
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!
DIF()
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
;
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!
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.