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!
Is time an actual SAS Time valie i.e. numeric?
Good question, it's numeric with the timeampm format.
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
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?
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?
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.
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;
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.