Hello,
I need to create customized time and date intervals for my time series data. I have a time stamp, and I want to classify the hours of the day into categories, as well as the day of the week. I need two additional columns, one that classifies the time into:
Time Category | Variable | Time Interval |
Early Morning | EM | 5AM - 8AM |
Morning | MO | 8AM - 11AM |
Lunch | LU | 11AM - 2PM |
Afternoon | AF | 2PM- 5PM |
Evening | EV | 5PM - 8PM |
Night | NI | 8PM - 10PM |
Late Night | LN | 10PM - 2AM |
Graveyard | GY | 2AM - 5AM |
I also want to do the same with Days of the week (Sun-Sat) and Month (Jan - Dec).
The time variable is LocalDateTime and is under the format DATETUNE. and looks like 03NOV13:14:42:33.
My data looks like this:
The data is called JanFile
USER | DEVICE | LOCALDATETIME | SECONDS_ACCESSED | LOCATION | MARKET |
2133310 | 18116198 | 30NOV13:16:03:21 | 45 | Away | UK |
2133310 | 18119318 | 03NOV13:12:46:56 | 95 | Away | UK |
2133310 | 18119318 | 03NOV13:13:57:43 | 264 | Away | UK |
2133310 | 18119318 | 09NOV13:08:25:12 | 1630 | Away | UK |
2133310 | 18119318 | 09NOV13:09:53:04 | 1222 | Away | UK |
2133310 | 18119318 | 09NOV13:09:23:31 | 1707 | Away | UK |
2133310 | 18119318 | 09NOV13:08:07:03 | 1089 | Away | UK |
2133310 | 18119318 | 03NOV13:12:45:10 | 75 | Away | UK |
2133310 | 18119318 | 17NOV13:14:45:38 | 5 | Away | UK |
2133310 | 18119318 | 23NOV13:22:58:06 | 917 | Away | UK |
2133310 | 18119318 | 29NOV13:22:29:39 | 5 | Away | UK |
2133310 | 18119318 | 04NOV13:08:02:17 | 119 | Away | UK |
2133310 | 18119318 | 10NOV13:09:59:20 | 5 | Away | UK |
2133310 | 18119318 | 15NOV13:16:57:02 | 5 | Away | UK |
2133310 | 18119318 | 19NOV13:07:29:18 | 1722 | Away | UK |
2133310 | 18119318 | 12NOV13:07:27:29 | 1277 | Away | UK |
2133310 | 18119318 | 29NOV13:18:45:04 | 5 | Away | UK |
2133310 | 18119318 | 25NOV13:07:28:52 | 1734 | Away | UK |
2133310 | 18119318 | 27NOV13:08:00:33 | 15 | Away | UK |
Please help!! Thank you!
Times in SAS are stored as numbers, in terms of seconds, so 60 represents 1 minute and 3600 represents 1 hour or 1AM.
Using that, determine what your boundaries should be and apply proc format. Here's an example for one column, you should be able to recreate for the other two. The day of the week/month can use sas functions such as WEEKDAY and MONTH.
*View boundaries (for display and explanation);
data hours_mapped;
do i=0 to 86400 by 3600;
format i time8.;
j=i;
output;
end;
run;
*create a format to represent the custom intervals. You should look up the meaning of the -< and make sure it works for you;
proc format;
value time_interval
18000 -< 28800 = '5AM - 8AM'
28800 -< 39600 = '8AM - 11AM'
39600 -< 50400 = '11AM - 2PM'
50400 -< 61200 = '2PM - 5PM'
61200 -< 72000 = '5PM - 8PM'
72000 -< 79200 = '8PM - 10PM'
0 -< 7200, 79200 - 86400 = '10PM - 2AM'
7200 -< 18000 = '2AM - 5AM';
run;
data want;
set have;
weekday=weekday(datepart(localdatetime));
month=month(datepart(localdatetime));
time=timepart(localdatetime);
format time time8.;
time_interval = put(time, time_interval.);
run;
You say you need two additional columns but show three?
Its much easier if you explain your problem as this is what I have and this is what I want, using two data steps.
Regardless, for converting I'd recommend looking into proc format.
Hi Reeza,
Sorry, I meant 3 additional columns. The columns that I want to create is the "Variable" column in the first table, and then 2 additional columns with "Day of the Week" and "Month of the Year". I included the first table to show the customized time interval by which I need to classify the time stamp.
Times in SAS are stored as numbers, in terms of seconds, so 60 represents 1 minute and 3600 represents 1 hour or 1AM.
Using that, determine what your boundaries should be and apply proc format. Here's an example for one column, you should be able to recreate for the other two. The day of the week/month can use sas functions such as WEEKDAY and MONTH.
*View boundaries (for display and explanation);
data hours_mapped;
do i=0 to 86400 by 3600;
format i time8.;
j=i;
output;
end;
run;
*create a format to represent the custom intervals. You should look up the meaning of the -< and make sure it works for you;
proc format;
value time_interval
18000 -< 28800 = '5AM - 8AM'
28800 -< 39600 = '8AM - 11AM'
39600 -< 50400 = '11AM - 2PM'
50400 -< 61200 = '2PM - 5PM'
61200 -< 72000 = '5PM - 8PM'
72000 -< 79200 = '8PM - 10PM'
0 -< 7200, 79200 - 86400 = '10PM - 2AM'
7200 -< 18000 = '2AM - 5AM';
run;
data want;
set have;
weekday=weekday(datepart(localdatetime));
month=month(datepart(localdatetime));
time=timepart(localdatetime);
format time time8.;
time_interval = put(time, time_interval.);
run;
Thank you very much!
Did you create a SAS datetime variable for your datetime? If so you can use the format DTMONYYw to write the month and year from the datetime directly such as OCT06 or OCT2006. You may get into issues just using MONTH unless you mean to combine usage from Jan 2013 with Jan 2014?
Format DTWKDATXw may be of use as it will display the datetime in day-of-week, monthname and year set the length short and you only get the day of week
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.