DATA Step, Macro, Functions and more

Hour-By-Hour Time Interval Indicators

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 18
Accepted Solution

Hour-By-Hour Time Interval Indicators

Hello,

Each observation indicates a datetime interval with variable Begin_Date_Time, and End_Date_Time.  Some observations span multiple days.

What I would like to do is create dummy variables such as d2h14 that would indicate that the 14th hour (2 pm) of the 2nd day is in the interval.  Another variable would indicate whether that hour is in a weekend or weekday is also needed.

Any suggestions?

I tremendously appreciate this community and those who offer a helping hand.


Accepted Solutions
Solution
‎10-07-2014 11:07 AM
Super User
Posts: 17,868

Re: Hour-By-Hour Time Interval Indicators

Assuming you have multiple records I would use something like the following, because you can then use SAS built in summary functions.  We use this structure for looking at ER peak times.

data have;

informat start end datetime21.;

format start end datetime21.;

input start End;

cards;

06DEC03:08:00:00 08DEC03:01:00:00

;

run;

data want;

set have;

format time datetime21.;

do time=start to end by 3600;

  day=day(datepart(time));

  hour=hour(timepart(time));

  if day in (2:6) then weekday=1; else weekday=0;

  output;

end;

run;

View solution in original post


All Replies
Super User
Posts: 17,868

Re: Hour-By-Hour Time Interval Indicators

I'd need to see some sample data/output of what is expected, not a wordy person, more of a data person Smiley Happy

Occasional Contributor
Posts: 18

Re: Hour-By-Hour Time Interval Indicators

Here is an example.

Begin_Date_Time          End_Date_Time

06DEC03:08:00:00          08DEC03:01:00:00

From that, I desire variables

d0h8     d0h9     ...      d3h0     d3h1

1              1                    1          1

and for each new variable an indicator variable denoting weekend or week.

The application for this involves mapping each hour within the interval to one of two probability distribution functions - one for weekdays, the other for weekends - that will then be used as weights in an index.  Feel free to come up with a better soltuion, but this way seems best to me.

Solution
‎10-07-2014 11:07 AM
Super User
Posts: 17,868

Re: Hour-By-Hour Time Interval Indicators

Assuming you have multiple records I would use something like the following, because you can then use SAS built in summary functions.  We use this structure for looking at ER peak times.

data have;

informat start end datetime21.;

format start end datetime21.;

input start End;

cards;

06DEC03:08:00:00 08DEC03:01:00:00

;

run;

data want;

set have;

format time datetime21.;

do time=start to end by 3600;

  day=day(datepart(time));

  hour=hour(timepart(time));

  if day in (2:6) then weekday=1; else weekday=0;

  output;

end;

run;

Occasional Contributor
Posts: 18

Re: Hour-By-Hour Time Interval Indicators

That worked splendidly!

For those interested, the resulting data set was not what I asked for but will work as good, but probably better.  Instead of new variables being created for each each hour, instead new observations are created.  This creates a lot of redundancy because all the other variables are dragged along into each newly created observation.  But that is not a problem.

The output for

Begin_Date_Time          End_Date_Time

06DEC03:08:00:00          08DEC03:01:00:00

would look like

time                                        day          hour          weekday               Other variable

06DEC03:08:00:00               6               8               1                              blah blah blah

06DEC03:09:00:00               6               9               1                              blah blah blah

...

08DEC03:01:00:00               1               1               0                              blah blah blah


This will work because I can use my probability distribution function table to weight each hour accordingly and then use proc means to create my index.


Thanks!  This has saved me hours (which means days).

Super User
Posts: 10,516

Re: Hour-By-Hour Time Interval Indicators

Are your Begin_date_time and End_date_time actually SAS datetime variables?

If so, the second part of your request, assuming you want your weekday variable to be 1 for Monday through Friday and 0 for Saturday and Sunday:

Weekdayflag = (weekday(datetpart(begin_date_time)) in (2,3,4,5,6));

"What I would like to do is create dummy variables such as d2h14 that would indicate that the 14th hour (2 pm) of the 2nd day is in the interval"

2nd day of what? week, month, year or something else.

Occasional Contributor
Posts: 18

Re: Hour-By-Hour Time Interval Indicators

Begin_date_time and End_date_time are actual SAS datetime variables.

d2h14 would refer to the 2pm of the 2nd day in the interval between Begin_date_time and End_date_time.

The intervals are typically between a few hours and 20 or so days, with most being about 1 day.

Thanks.

Super User
Posts: 17,868

Re: Hour-By-Hour Time Interval Indicators

This is possible, but I question why you want to do it. It may be worth explaining your overall goals and seeing if there isn't a more efficient way of accomplishing it.  Otherwise you may end up with a very wide data set that's unweildly to Handel in the future.

Occasional Contributor
Posts: 18

Re: Hour-By-Hour Time Interval Indicators

The width of the data set will be manageable.

Super User
Posts: 10,516

Re: Hour-By-Hour Time Interval Indicators

Exactly 2pm or some range of 2:00pm to 2:xxpm?

You'll want to become familiar with the date and time functions such as DATEPART, TIMEPART, HOUR and INTCK.

I still think you want to post some examples of your values and what you expect for output.

Occasional Contributor
Posts: 18

Re: Hour-By-Hour Time Interval Indicators

In my attempts, I have become familiar with those functions: they will surely be used.  My approach has been to use INTCK to set the upper bound in a DO loop and sequentially add hours.  I have not been able to get this to work satisfactory, however.  One obstacle is naming the variables as I go based on the day and hour.

☑ This topic is SOLVED.

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

Discussion stats
  • 10 replies
  • 310 views
  • 3 likes
  • 3 in conversation