Solved
Contributor
Posts: 27

# Programming to create this table

Hello,

I have a table of a patients' hospital stay, their intervention, the time(s) they started and stopped the intervention (in hours:minutes:seconds).

For example:

Hospital DayIntervention StepStart timeEnd time
1114:57:00
21
323:31:00
42
52
6112:32:00
71
819:51:00

1) This particular patient was on the intervention for a total of 8 days and transitioned between step 1 and 2 of the intervention (there are a total of 4 steps - steps 0, 1, 2, and 3).

2) The patient started step 1 on day 1 at 14:57:00 and continued on this step through the 2nd day and part of the 3rd day.On the 3rd day, the patient switched to step 2 at 3:31:00 and continued on step 2 through the 5th day and part of the 6th day. On the 6th day, the patient switched back to step 1 at 12:32:00 and finished on step 1 at 9:51:00 on the 8th day.

In the end, I would like a table like this:

Intervention StepTotal time (hrs)
181.88
281.02

Note that I've collapsed the intervention steps into just 1 and 2. Additionally, I've accounted for any transition time from one step to another. For example, on day 3 when the patient switched at 3:31:00, there is a gap of time that day (3 hrs, 31 min) when the patient was on step 1, prior to switching to step 2. The gap of time should be added to the total time spent on step 1.

John

Accepted Solutions
Solution
‎05-01-2014 04:38 AM
Super User
Posts: 10,766

## Re: Programming to create this table

Use NULL 's data .

```data time;
infile cards missover expandtabs;
input day step (start end)(:time.);
format start end time.;
cards;
1  1  14:57:00
2  1
3  2  3:31:00
4  2
5  2
6  1  12:32:00
7  1
8  1  .  9:51:00
;;;;
run;
data temp;
set time;
by step notsorted;
dur=coalesce(end,'24:00:00't)-coalesce(start,'00:00:00't);output;
lag_step=lag(step);
if first.step and day ne 1 then do;
dur=coalesce(start,'00:00:00't)-'00:00:00't;
step=lag_step;
output;
end;
format dur hour8.2;
keep step dur;
run;
proc summary data=temp nway;
class step;
var dur;
output out=want(drop=_:) sum=dur_sum;
run;

```

Xia Keshan

All Replies
Super User
Posts: 23,683

## Re: Programming to create this table

If your hospital day is actually a date, make this a datetime variable instead, then you can do basic time addition/subtraction.

Super User
Posts: 13,508

## Re: Programming to create this table

Does the data collection always skip the end time if another intervention is started?

Are there more than 2 possible interventions?

Contributor
Posts: 27

## Re: Programming to create this table

Yes, there are 4 steps to the intervention - steps 0 through 3. I have 296 of these datasets - there is one sas dataset per patient (that's how I received it since there is more information that I won't be using). From the few that I've looked through, it seems that if a patient switches from one step to another, there is no stop time - just a transition to the next step at a specified start time. The stop time seems to only be for the last day the patient is on the intervention. The structure in terms of headers are the same, but patients will have different lengths of stay and different start and stop time patterns (e.g a patient may be on a single step throughout the duration and will have just one start time and one stop time).

Super User
Posts: 23,683

## Re: Programming to create this table

Day 1 - Step 1 - 9 hours 3 minutes

Day 2 - Step 1 - 24 hours

Day 3 - Step 1 - 3 hours 31 mins

Day 3 - Step 2 - 20 hours 29 mins

Day 4 - Step 2 - 24 hours

Day 5 - Step 2 - 24 hours

Day 6 - Step 1 - 11 hours 28 mins

Day 7 - Step 1 - 24 hours

Day 8 - Step 1 - 9 hours, 51 minutes

81.88

So I don't have time to code this, but I'd go through and calculate the portion of each day at each step, as above

Some things to consider, you'll need to use the lag function to see the previous step when it changes at the middle of the day and 24-time on the first day to get the remaining hours.

Then you can use proc means to sum the total by patient, intervention step.

Contributor
Posts: 27

## Re: Programming to create this table

Yes, I had that sort of structure in my initial attempt at coding. I created three variables corresponding to the total time on the intervention for each day. The first variable corresponds to the times when the patient is on step 1 the first time, the 2nd when the patient is on step 2 and the 3rd variable when the patient is reinstated into step 1. However, I was having difficulty doing the "bleed-in" time when the patient transitions.

Posts: 3,852

## Re: Programming to create this table

This works for your example data.  I would be good to see more data that is closer to the actual so we can modify this accordingly.

data time;
infile cards missover expandtabs;

input day step (start end)(:time.);
s = dhms(day-1,0,0,start);
e = dhms(day-1,0,0,end);
format start end time. s e datetime.;

cards;
1  1  14:57:00
2  1
3  2  3:31:00
4  2
5  2
6  1  12:32:00
7  1
8  1  .  9:51:00
;;;;
run;
proc print;

run;
data time2(keep=day step t);
set time end=eof;
by step notsorted;

if last.step and not eof then do;
point = _n_ +
1;

set time(keep=s rename=(s=ns)) point=point;
end;
e=coalesce(e,ns);
t=coalesce(s,e);

output;

call missing(of _all_);
format t datetime.;

run;
proc print;

run;
proc summary data=time2 nway;

by step notsorted;

output out=range range(t)=duration;
run;
proc summary data=range nway;

class step;
output out=sum sum(duration)=;
format duration: hour8.2;

run;
proc print;

run;
Solution
‎05-01-2014 04:38 AM
Super User
Posts: 10,766

## Re: Programming to create this table

Use NULL 's data .

```data time;
infile cards missover expandtabs;
input day step (start end)(:time.);
format start end time.;
cards;
1  1  14:57:00
2  1
3  2  3:31:00
4  2
5  2
6  1  12:32:00
7  1
8  1  .  9:51:00
;;;;
run;
data temp;
set time;
by step notsorted;
dur=coalesce(end,'24:00:00't)-coalesce(start,'00:00:00't);output;
lag_step=lag(step);
if first.step and day ne 1 then do;
dur=coalesce(start,'00:00:00't)-'00:00:00't;
step=lag_step;
output;
end;
format dur hour8.2;
keep step dur;
run;
proc summary data=temp nway;
class step;
var dur;
output out=want(drop=_:) sum=dur_sum;
run;

```

Xia Keshan

Contributor
Posts: 27

## Re: Programming to create this table

Wow thank you for all your responses!

🔒 This topic is solved and locked.