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!
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.
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.