topic How to calculate the time difference between two observations of the single date variable in New SAS User

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

SAS_USER_928 — Fri, 26 Nov 2021 19:08:38 GMT

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.

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

PaigeMiller — Fri, 26 Nov 2021 19:51:53 GMT

/* 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.

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

SAS_USER_928 — Fri, 26 Nov 2021 19:56:25 GMT

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.

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

PaigeMiller — Fri, 26 Nov 2021 20:06:26 GMT

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

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

SAS_USER_928 — Fri, 26 Nov 2021 20:14:01 GMT

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

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

PaigeMiller — Fri, 26 Nov 2021 20:24:40 GMT

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

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

SAS_USER_928 — Fri, 26 Nov 2021 22:47:39 GMT

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.

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

SAS_USER_928 — Fri, 26 Nov 2021 22:55:15 GMT

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

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

PaigeMiller — Fri, 26 Nov 2021 23:44:58 GMT

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

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

SAS_USER_928 — Fri, 26 Nov 2021 23:54:55 GMT

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.

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

PaigeMiller — Sat, 27 Nov 2021 10:42:48 GMT

Then use the formula for hours I provided earlier.

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

SAS_USER_928 — Tue, 01 Mar 2022 19:20:53 GMT

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.

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

PaigeMiller — Tue, 01 Mar 2022 19:34:43 GMT

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

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

SAS_USER_928 — Tue, 01 Mar 2022 21:39:19 GMT

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