topic Re: Create Customized Time Intervals in SAS Procedures

Create Customized Time Intervals

ndvorak_forrester_com — Thu, 20 Feb 2014 14:24:37 GMT

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!

Re: Create Customized Time Intervals

Reeza — Thu, 20 Feb 2014 15:28:41 GMT

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.

Re: Create Customized Time Intervals

ndvorak_forrester_com — Thu, 20 Feb 2014 15:31:54 GMT

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.

Re: Create Customized Time Intervals

Reeza — Thu, 20 Feb 2014 16:09:26 GMT

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;

Re: Create Customized Time Intervals

ballardw — Thu, 20 Feb 2014 16:33:30 GMT

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

Re: Create Customized Time Intervals

ndvorak_forrester_com — Thu, 20 Feb 2014 17:54:13 GMT

Thank you very much!