topic Re: Hour-By-Hour Time Interval Indicators in SAS Programming

Hour-By-Hour Time Interval Indicators

SColby — Tue, 07 Oct 2014 14:22:41 GMT

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.

Re: Hour-By-Hour Time Interval Indicators

Reeza — Tue, 07 Oct 2014 14:29:24 GMT

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

Re: Hour-By-Hour Time Interval Indicators

ballardw — Tue, 07 Oct 2014 14:38:50 GMT

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.

Re: Hour-By-Hour Time Interval Indicators

SColby — Tue, 07 Oct 2014 14:44:44 GMT

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.

Re: Hour-By-Hour Time Interval Indicators

Reeza — Tue, 07 Oct 2014 14:47:51 GMT

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.

Re: Hour-By-Hour Time Interval Indicators

ballardw — Tue, 07 Oct 2014 14:52:23 GMT

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.

Re: Hour-By-Hour Time Interval Indicators

SColby — Tue, 07 Oct 2014 14:55:25 GMT

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.

Re: Hour-By-Hour Time Interval Indicators

SColby — Tue, 07 Oct 2014 14:57:20 GMT

The width of the data set will be manageable.

Re: Hour-By-Hour Time Interval Indicators

SColby — Tue, 07 Oct 2014 15:02:15 GMT

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.

Re: Hour-By-Hour Time Interval Indicators

Reeza — Tue, 07 Oct 2014 15:07:06 GMT

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;

Re: Hour-By-Hour Time Interval Indicators

SColby — Tue, 07 Oct 2014 15:28:02 GMT

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).