Data Preprocessing Using SAS - 수술실 사용시간 분포 구하기

<문제>. 수술ID, 수술시작시간, 수술끝시간 이 저장된 데이타로부터 각 시간대별 수술실 사용현황을 구하시오.

<Data Sample형태>

surgery_time_sample.sas7bdat

       

  OBS operation_ID         start_time              end_time

    1    A3B1827B20    19DEC09:10:14:00    19DEC09:11:20:00

    2    A3B2842B20    17FEB10:15:03:00    17FEB10:19:51:00

    3    A3B3508B20    06NOV09:09:59:00    06NOV09:12:02:00

    4    A3B3531B20    28APR10:07:58:00    28APR10:08:46:00

    5    A3B3679B20    21JUL09:08:00:00    21JUL09:09:31:00

    6    A3B3706B20    30APR09:09:49:00    30APR09:10:41:00

    7    A3B3746B20    15JUL09:10:03:00    15JUL09:10:20:00

    8    A3B3780B20    15MAY09:09:48:00    15MAY09:17:23:00

    9    A3B3786B20    05MAR09:07:52:00    05MAR09:13:06:00

   10    A3B3801B20    04MAY09:11:42:00    04MAY09:13:48:00

   11    A3B3807B20    08MAY09:08:19:00    08MAY09:10:43:00

   12    A3B3825B20    01DEC09:09:14:00    01DEC09:09:30:00

   13    A3B3842B20    19JUN09:12:58:00    19JUN09:20:15:00

   14    A3B3843B20    25JUN09:16:05:00    25JUN09:18:52:00

   15    A3B3850B20    15JUL09:08:07:00    15JUL09:09:21:00

   16    A3B3902B20    29OCT09:10:26:00    29OCT09:13:15:00

   17    A3B3908B20    12JAN10:12:16:00    12JAN10:17:04:00

   18    A3B4008B20    26MAY10:09:18:00    26MAY10:12:13:00

   19    A3B4019B20    29JUN10:07:50:00    29JUN10:11:22:00

   20    A3B4020B20    07JUN10:16:22:00    07JUN10:19:29:00

<SAS Program>

surgery_time_analy01.sas

/****************************************************************************

수술실별 * 시간대별 사용현황

*/

libname myset "C:\data";

data smpl01;

set myset.surgery_time_sample;

run;

****************************************************************************;

*시간대별 수술실 점유시간, 총 수술시간 구하기;

*Compute Time;

data smpl02;

set smpl01;

total_time = intck( 'minute', start_time, end_time) ; /*총수술시간*/

if total_time = . then delete; /*수술퇴실시간 없는 경우 삭제*/

run;

*수술건별 시간대별 사용시간 체크;

data smpl03(drop= i);

set smpl02;

ed_line = end_time - (minute(end_time)*60); /*마지막 시간*/

format ed_line datetime16.;

do i=1 to 10000 while(start_time < ed_line);

if i = 1 then output;

start_time = start_time + (60*60);

output;

end;

run;

*시간대별 정시 설정;

data smpl04;

set smpl03;

range_from = hms(hour(start_time),00,00);

range_to = hms(hour(start_time) + 1,00,00);

format range_from range_to time8.;

run;

*정시로부터 수술실 점유시간;

proc sort data = smpl04;

by operation_id start_time;

data smpl05;

set smpl04;

by operation_id start_time;

if first.operation_id then cover_time = intck( 'minute', timepart(start_time), range_to) ;

else if last.operation_id then cover_time = intck( 'minute',range_from, timepart(end_time)) ;

else cover_time = 60;

if first.rec_id = 1 and last.rec_id = 1 then cover_time = total_time; /*동시간에 수술이 끝날경우*/

run;

*검증 : 총 수술사용시간 = sum(시간대별 점유시간);

proc sql;

create table checking01 as

select operation_id

,sum(cover_time)

, total_time

from smpl05

group by operation_id;

quit;

data checking02;

set checking01;

where _TEMG001 ^= total_time;

run;

/*수술실별 시간대별 사용시간*/

ods listing close;

ods html;

proc means data = smpl05 sum maxdec=0 ;

class op_room_num range_from;

var cover_time;

run;

ods listing;

ods html close;