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

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

10 REPLIES 10
Reeza
Super User

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

SColby
Calcite | Level 5

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.

Reeza
Super User

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;

SColby
Calcite | Level 5

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

ballardw
Super User

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.

SColby
Calcite | Level 5

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.

Reeza
Super User

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.

SColby
Calcite | Level 5

The width of the data set will be manageable.

ballardw
Super User

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.

SColby
Calcite | Level 5

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.

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 10 replies
  • 1639 views
  • 3 likes
  • 3 in conversation