topic Re: How to Clean Data Fields with Multiple Time Strings to Calculate Time Interval in SAS Programming

How to Clean Data Fields with Multiple Time Strings to Calculate Time Interval

eap — Fri, 02 Aug 2019 14:09:48 GMT

Hi,

I was given a huge dataset containing 2 data fields (procedurestarttime & procedureendtime) with multiple time formats (example below). The time interval values are not always correct. Therefore, I want to calculate the time interval from start and end time and only use the inputted time_interval field only when everything else is missing.

I imagine I need a seires of IF THEN DO statements to separately strip the dates, pm, AM and insert ‘:’s when necessary followed by informats/formats. I’m not sure how to identify the different string types in order to apply a conversion.

I'd appreciate any suggestions regarding possible approaches. Thanks!

ProcedureStarttime ProcedureEndtime    Time_interval
14:08 15:31 1:23
8:56:00    10:40:00    1:44:00
7:14:00 AM    8:14:00 AM 1:00:00
1/23/2018 11:14    1/23/2018 12:03    12:49:00 AM
2/8/2018 18:47    2/8/2018 19:50 1:03
10:00am    12:00pm    120
1350    1532    Not Reported
01-06-2008 09:35 01-06-2008 11:10 1:35

10:00am 12:00pm 120
1350 1532 Not Reported
01-06-2008 09:35 01-06-2008 11:10 1:35

data have1;
set have;

if procedurestarttime="DON'T KNOW" then do;
  starttime = tranwrd(procedurestarttime, '/ /', ' ');
end;
if procedurestarttime="DON'T KNOW" then do;
  starttime = tranwrd(procedurestarttime,'AM', ' ');
end;
if procedurestarttime="DON'T KNOW" then do;
starttime = timepart(procedurestarttime);
end;

/*Do same for procedurendtime*/


run;

Re: How to Clean Data Fields with Multiple Time Strings to Calculate Time Interval

Reeza — Fri, 02 Aug 2019 14:48:19 GMT

Does the above indicate an example of one of each type of what you may have? If not, can you increase the sample to include at least one of each type of date/time you have.

Re: How to Clean Data Fields with Multiple Time Strings to Calculate Time Interval

ballardw — Fri, 02 Aug 2019 15:06:25 GMT

If you are willing to work with actual time values instead of strings this does most of what you want. The "not reported" being an ambiguous time value end up as missing values.

data example;
   infile datalines dsd dlm=',';
   informat str $20.;
   input str;
   if length(str)> 10 then do;
      dt = input(str,anydtdtm32.);
      time= timepart(dt);
   end;
   else time= input(str,time8.);
   format time time8.;
datalines;
"14:08"             
"8:56:00"           
"7:14:00 AM"        
"1/23/2018 11:14"   
"2/8/2018 18:47"    
"10:00am"           
"1350"              
"01-06-2008 09:35"  
"10:00am"           
"1350"              
"01-06-2008 09:35"
;

the key part is in the if /then/else. You didn't provide actual data so I dummied up your values into something to manipulate.

Time values I suspect in the long run will be easier to work with in the long run as you can use the time functions to pull out things like hour or minute or intervals with the INTCK function or increment with INTNX. The format I assigned, TIME8. will show time using a 24 hour clock. If you want to see AM/PM then you could use the TIMEAMPM format. Or even create a custom appearance using the Proc Format Picture statement.

Re: How to Clean Data Fields with Multiple Time Strings to Calculate Time Interval

eap — Fri, 02 Aug 2019 16:26:59 GMT

With the exception where one or all 3 fields have missing values, the above is a representative sample of the combinations found in the dataset.

Re: How to Clean Data Fields with Multiple Time Strings to Calculate Time Interval

Tom — Fri, 02 Aug 2019 16:54:09 GMT

You probably need to use different INFORMAT based on how the data looks. Here is method that uses / or - to indicate that date is included. Then checks whether : is included to check for whether to use HHMMSS informat or not.

data have;
  infile cards dsd truncover dlm='|';
  input ProcedureStarttime :$32. ProcedureEndtime :$32. Time_interval :$32. ;
cards;
14:08 | 15:31 | 1:23
8:56:00 | 10:40:00 | 1:44:00
7:14:00 AM | 8:14:00 AM | 1:00:00
1/23/2018 11:14 | 1/23/2018 12:03 | 12:49:00 AM
2/8/2018 18:47 | 2/8/2018 19:50 | 1:03
10:00am | 12:00pm | 120
1350 | 1532 | Not Reported
01-06-2008 09:35 | 01-06-2008 11:10 | 1:35
 | |
10:00am | 12:00pm | 120
1350 | 1532 | Not Reported
01-06-2008 09:35 | 01-06-2008 11:10 | 1:35
;
data want ;
  set have;
  length want $32 ;
  if cmiss(ProcedureStarttime,ProcedureEndtime) then want=' ';
  else if indexc(cats(ProcedureStarttime,ProcedureEndtime),'/-') then do;
    time_diff = input(ProcedureEndtime,anydtdtm32.)-input(ProcedureStarttime,anydtdtm32.);
  end;
  else if indexc(cats(ProcedureStarttime,ProcedureEndtime),':') then do;
    time_diff = input(ProcedureEndtime,anydttme32.)-input(ProcedureStarttime,anydttme32.);
  end;
  else do;
    time_diff = input(ProcedureEndtime,hhmmss.)-input(ProcedureStarttime,hhmmss.);
  end;
  if not missing(time_diff) then want=put(time_diff,hhmm10.);
  drop time_diff;
run;

proc print;
run;

       Procedure                               Time_
Obs    Starttime           ProcedureEndtime    interval        want

  1    14:08               15:31               1:23            1:23
  2    8:56:00             10:40:00            1:44:00         1:44
  3    7:14:00 AM          8:14:00 AM          1:00:00         1:00
  4    1/23/2018 11:14     1/23/2018 12:03     12:49:00 AM     0:49
  5    2/8/2018 18:47      2/8/2018 19:50      1:03            1:03
  6    10:00am             12:00pm             120             2:00
  7    1350                1532                Not Reported    1:42
  8    01-06-2008 09:35    01-06-2008 11:10    1:35            1:35
  9
 10    10:00am             12:00pm             120             2:00
 11    1350                1532                Not Reported    1:42
 12    01-06-2008 09:35    01-06-2008 11:10    1:35            1:35

Re: How to Clean Data Fields with Multiple Time Strings to Calculate Time Interval

ballardw — Fri, 02 Aug 2019 16:58:38 GMT

@eap wrote:

With the exception where one or all 3 fields have missing values, the above is a representative sample of the combinations found in the dataset.

Is this a response to something? Can not tell what it relates to.

Re: How to Clean Data Fields with Multiple Time Strings to Calculate Time Interval

eap — Fri, 02 Aug 2019 19:29:04 GMT

This works perfectly! Thank you!