How to calculate the time difference between two observations of the single date variable

[SAS_USER_928]

Hi @mkeintz, @PGStats, @Reeza, @FreelanceReinh, @novinosrin 

I have a data set that contains the patients' dosing data. Every patient is supposed to take 2 doses daily every 12 hours (+-30 minutes). I need to calculate the difference between the time (per date per patient) and check which patients have or have not taken their doses correctly. 

The data set has only the following two variables: PT, Dose Time.

e.g., 

30SEP2021:22:31:00	1
30SEP2021:10:30:00	1
29SEP2021:22:28:00	1
29SEP2021:10:30:00	1
28SEP2021:22:30:00	1
28SEP2021:10:30:00	1
27SEP2021:22:30:00	2
27SEP2021:10:28:00	2
26SEP2021:22:30:00	2
26SEP2021:10:30:00	2
25SEP2021:22:30:00	3
25SEP2021:10:30:00	3
24SEP2021:22:30:00	3
24SEP2021:11:30:00	3
03OCT2021:21:42:00	3
03OCT2021:10:34:00	3

Thank you so much and appreciate your help.

[PaigeMiller · Accepted solution]

/* UNTESTED CODE */
data want;
    set have;
    by pt;
    delta_time=dose_time - lag(dose_time);
    if first.pt then delta_time=.;
    format delta_time time.;
run;

This assumes the data is properly sorted by PT and by dose_time. It also assumes that dose_time is numeric and not character.

If you want tested code, do not provide data as screen captures. Provide data using these instructions.

[SAS_USER_928]

Thank you so much @PaigeMiller. This did work. I just updated the following line to get the number of hours between two doses (I believe the prior result was in seconds?).

delta_time=dose_time - lag(dose_time)/3600; 

Note: The dose dates were in DateTime format.

Thank you so much again for your prompt response and solution. 

[PaigeMiller]

@SAS_USER_928 

I believe you want

delta_time=(dose_time - lag(dose_time))/3600; 

but since my original solution was formatted with the time. format, I don't see any benefit of doing this division.

[SAS_USER_928]

Hi @PaigeMiller ,

That's correct. Sorry I probably deleted additional parenthesis while updating the original variables here for public use.

DELTA_TIME= ROUND((DOSE_DT_TIME- LAG(DOSE_DT_TIME))/3600, 0.01);

The original answer had values like 45000 etc. in the DELTA_TIME variable which I did not want. That's the reason I divided by 3600 to get something like 12(.35) (hours).

Thank you

[PaigeMiller]

Yes, I get that, but using the TIME. format essentially displays the result as (for example) 12:05:14

[SAS_USER_928]

Ah that TIME. formatted variable is perfect; I want it but cannot use to calculate/apply Mean statistics on it. That's why I created a new variable and converted to the numeric number of hours as well.

[SAS_USER_928]

OK now I need a little more help. The new variable that I created (that holds the number of hours) is not correct. Can you please help to use your TIME. formatted variable to calculate the number of hours (as number variable) so that I can apply Mean on it?

Thank you

[PaigeMiller]

---

@SAS_USER_928 wrote:

Ah that TIME. formatted variable is perfect; I want it but cannot use to calculate/apply Mean statistics on it. That's why I created a new variable and converted to the numeric number of hours as well.

---

Incorrect. You can calculate means on it just as you can calculate means on any other number.

OK now I need a little more help. The new variable that I created (that holds the number of hours) is not correct. Can you please help to use your TIME. formatted variable to calculate the number of hours (as number variable) so that I can apply Mean on it?

PROC MEANS will do it. It works just fine on formatted variables.

[SAS_USER_928]

Hi @PaigeMiller ,

No not in SAS application. I create a data set which I then import to a third party application (part of my job). They provide their own set of tests based on the types of variables. Mean was disabled for this character TIME formatted variable. That's the reason I need a numeric variable to hold the number of hours (as number and not as time).

Any help in the matter will be greatly appreciated. I am trying to get it to work but failing miserably.

[PaigeMiller]

Then use the formula for hours I provided earlier.

[SAS_USER_928]

Hi @PaigeMiller,

This time I have a different situation and thought if you could please help... using SAS or Proc SQL.

I have a list of pts. that have to complete a lab every N (say between 4 to 5) weeks and outside of this window will be considered NON COMPLIANT.

Here is the list of PTs and their lab dates:

PT	Date	Duration in Weeks
1	1/5/2021
1	2/24/2021
1	4/7/2021
1	5/19/2021
2	1/14/2021
2	3/2/2021
3	2/25/2022

My data is sorted by PT and Date in ascending order.  For PT1, I need to take the difference between every two labs and calculate the difference (duration) in weeks. If there is only 1 lab performed so far (as is the case with PT3) then use today's date to calculate the duration in weeks.

Thanking you in advance for your help. 

[PaigeMiller]

data want;
    set have;
    by pt;
    prev_date=lag(date);
    if first.pt then prev_date=.;
    duration_in_weeks = (date-prev_date)/7;
run;

[SAS_USER_928]

Thank you @PaigeMiller. There is only 1 scenario left out. See PT3 which has only one observation and I do not want to exclude that and want the Duration to be calculated as Extraction Date - (Lab) Date to see if the lab is past-due.

Note: Extraction Date is hard-code as 2022-03-01.

Thank you