Help using Base SAS procedures

Create Customized Time Intervals

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 9
Accepted Solution

Create Customized Time Intervals

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 CategoryVariableTime Interval
Early MorningEM5AM - 8AM
MorningMO8AM - 11AM
LunchLU11AM - 2PM
AfternoonAF2PM- 5PM
EveningEV5PM - 8PM
NightNI8PM - 10PM
Late NightLN10PM - 2AM
GraveyardGY2AM - 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

USERDEVICELOCALDATETIMESECONDS_ACCESSEDLOCATIONMARKET
21333101811619830NOV13:16:03:2145AwayUK
21333101811931803NOV13:12:46:5695AwayUK
21333101811931803NOV13:13:57:43264AwayUK
21333101811931809NOV13:08:25:121630AwayUK
21333101811931809NOV13:09:53:041222AwayUK
21333101811931809NOV13:09:23:311707AwayUK
21333101811931809NOV13:08:07:031089AwayUK
21333101811931803NOV13:12:45:1075AwayUK
21333101811931817NOV13:14:45:385AwayUK
21333101811931823NOV13:22:58:06917AwayUK
21333101811931829NOV13:22:29:395AwayUK
21333101811931804NOV13:08:02:17119AwayUK
21333101811931810NOV13:09:59:205AwayUK
21333101811931815NOV13:16:57:025AwayUK
21333101811931819NOV13:07:29:181722AwayUK
21333101811931812NOV13:07:27:291277AwayUK
21333101811931829NOV13:18:45:045AwayUK
21333101811931825NOV13:07:28:521734AwayUK
21333101811931827NOV13:08:00:3315AwayUK

Please help!! Thank you!


Accepted Solutions
Solution
‎02-20-2014 11:09 AM
Super User
Posts: 17,842

Re: Create Customized Time Intervals

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;

View solution in original post


All Replies
Super User
Posts: 17,842

Re: Create Customized Time Intervals

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.

Occasional Contributor
Posts: 9

Re: Create Customized Time Intervals

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.

Solution
‎02-20-2014 11:09 AM
Super User
Posts: 17,842

Re: Create Customized Time Intervals

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;

Occasional Contributor
Posts: 9

Re: Create Customized Time Intervals

Thank you very much!

Super User
Posts: 10,511

Re: Create Customized Time Intervals

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 540 views
  • 3 likes
  • 3 in conversation