BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

5 REPLIES 5
Reeza
Super User

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.

ndvorak_forrester_com
Calcite | Level 5

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.

Reeza
Super User

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;

ndvorak_forrester_com
Calcite | Level 5

Thank you very much!

ballardw
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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