IF then with TIMEAMPM data

[hwangnyc]

Hello everyone,

I'm not having much luck re-coding my time variable. What I would like is:

7AM-3PM = 'AM'

3PM-11PM = 'PM'

11PM-7AM = 'OVERNIGHT'

I have data that looks like this 

HAVE 

ID	TIME
102028027	4:00:00 AM
102028032	6:45:00 AM
102028088	3:15:00 PM
102028089	9:00:00 AM
102028049	1:30:00 PM

WANT

ID	TIME	SHIFT
102028027	4:00:00 AM	Overnight
102028032	6:45:00 AM	Overnight
102028088	3:15:00 PM	PM
102028089	9:00:00 AM	AM
102028049	1:30:00 PM	AM

Any help would be greatly appreciated!

Thank you! 

[PeterClemmensen]

Is time an actual SAS Time valie i.e. numeric?

[hwangnyc]

Good question, it's numeric with the timeampm format. 

[PeterClemmensen]

Do something like this

data have;
input ID TIME : time9.;
format TIME time9.;
datalines;
102028027 4:00:00AM
102028032 6:45:00AM
102028088 3:15:00PM
102028089 9:00:00AM
102028049 1:30:00PM
;

proc format;
   value ampm 
      '07:00't <- '15:00't = 'Overnight'
      '15:00't <- '23:00't = 'PM'
      other                = 'AM';
run;

data want;
   set have;
   shift=put(TIME, ampm.);
run;

Result:

ID          TIME       shift 
102028027   4:00:00    AM 
102028032   6:45:00    AM 
102028088   15:15:00   PM 
102028089   9:00:00    Overnight 
102028049   13:30:00   Overnight 

[Krueger]

data have ;  
infile cards dsd dlm=","; 
  input ID $ TIME : TIME11.;
  format TIME TIMEAMPM11.;
  cards ;                       
102028027, 4:00:00 AM,
102028032, 6:45:00 AM,
102028088, 3:15:00 PM,
102028089, 9:00:00 AM,
102028049, 1:30:00 PM
;
run;

data want;
set have;
IF '15:00:00't <= TIME <= '22:59:59't THEN SHIFT = 'PM ';
IF '07:00:00't <= TIME <= '14:59:59't THEN SHIFT = 'AM ';
IF '23:00:00't <= TIME <= '23:59:59't THEN SHIFT = 'Overnight';
IF '00:00:00't <= TIME <= '06:59:59't THEN SHIFT = 'Overnight';
RUN;

Here's what I came up with. I wasn't able to come up with an alternative for the overnight time maybe someone can expand on that?

[Reeza]

Do you have missing values that need to be considered? 

data want;
set have;
IF '15:00:00't <= TIME <= '22:59:59't THEN SHIFT = 'PM ';
ELSE IF '07:00:00't <= TIME <= '14:59:59't THEN SHIFT = 'AM ';
ELSE IF not missing(time) then SHIFT = 'Overnight';

RUN;

---

@Krueger wrote:

data have ;  
infile cards dsd dlm=","; 
  input ID $ TIME : TIME11.;
  format TIME TIMEAMPM11.;
  cards ;                       
102028027, 4:00:00 AM,
102028032, 6:45:00 AM,
102028088, 3:15:00 PM,
102028089, 9:00:00 AM,
102028049, 1:30:00 PM
;
run;

data want;
set have;
IF '15:00:00't <= TIME <= '22:59:59't THEN SHIFT = 'PM ';
IF '07:00:00't <= TIME <= '14:59:59't THEN SHIFT = 'AM ';
IF '23:00:00't <= TIME <= '23:59:59't THEN SHIFT = 'Overnight';
IF '00:00:00't <= TIME <= '06:59:59't THEN SHIFT = 'Overnight';
RUN;

Here's what I came up with. I wasn't able to come up with an alternative for the overnight time maybe someone can expand on that?

---

[Krueger]

I wasn't chaining ELSE IF which was my problem, instead I was trying...

IF THEN 1...

IF THEN 2...

ELSE 3...

and it was giving me 1 OR 3 not 1, 2 OR 3.

[ScottBass]

proc format;
   value shift
      '00:00:00't -< '07:00:00't = 'OVERNIGHT'
      '07:00:00't -< '15:00:00't = 'AM'
      '15:00:00't -< '23:00:00't = 'PM'
      '23:00:00't -< '23:59:59.9999999't = 'OVERNIGHT' /* only needed if you have fractional time */
   ;
run;

data have;
   do time='00:00:00't to '23:59:59't /* by (60*60) */ ;
      shift=put(time,shift.);
      output;
   end;

   * edge case, not needed if you don't have fractional time ;
   time='23:59:59.999't;
   shift=put(time,shift.);
   output;
   format time time15.6;
run;

proc sort data=have;
   by shift time;
run;

data review;
   set have;
   by shift time;
   if first.shift or last.shift or time in ('23:00:00't,'00:00:00't);
run;