Creating Bins for Times

[aperansi]

I am wanting to create bins for different time stamps. For example, if a SCORE_DATETIME HAPPENS BETWEEN 12:00AM and 1:00AM I would like to create a bin named " <1:00AM. So on and so fourth until i get to 24:00 hours. 

Im not sure how to code  this but here is my best guess at it. Can someone please help me figure out how to code this?

data August2019Bins;
set work.TimeDay6;
length ScoreTime $10.;
if SCORE_DATETIME = 00 then ScoreTime = "12:00AM";
if  1 =< SCORE_DATETIME < 2 then ScoreTime = " <= 1:00AM";
if  2 =< SCORE_DATETIME < 3 then ScoreTime = "<= 2:00AM";
if  3 =< SCORE_DATETIME < 4 then ScoreTime = "<= 3:00AM";
if  4 =< SCORE_DATETIME < 5 then ScoreTime = "<= 4:00AM";
if  5 =< SCORE_DATETIME < 6 then ScoreTime = "<= 5:00AM";
if  6 =< SCORE_DATETIME < 7 then ScoreTime = "<= 6:00AM";
if  7 =< SCORE_DATETIME < 8 then ScoreTime = "<= 7:00AM";
if  8 =< SCORE_DATETIME < 9 then ScoreTime = "<= 8:00AM";
if  9 =< SCORE_DATETIME < 10 then ScoreTime = "<= 9:00AM";
if  10 =< SCORE_DATETIME < 11 then ScoreTime = "<= 10:00AM";
if  11 =< SCORE_DATETIME < 12  then ScoreTime = "<= 11:00AM";
if  12 =< SCORE_DATETIME < 13 then ScoreTime = "<= 12:00PM";
if 13 =< SCORE_DATETIME < 14 then ScoreTime = "<= 1:00PM";
if 14 =< SCORE_DATETIME < 15 then ScoreTime = "<= 2:00PM";
if 15 =< SCORE_DATETIME < 16 then ScoreTime = "<= 3:00PM";
if 16 =< SCORE_DATETIME < 17 then ScoreTime = "<= 4:00PM";
if 17 =< SCORE_DATETIME < 18 then ScoreTime = "<= 5:00PM";
if 18 =< SCORE_DATETIME < 19 then ScoreTime = "<= 6:00PM";
if 19 =< SCORE_DATETIME < 20 then ScoreTime = "<= 7:00PM";
if 21 =< SCORE_DATETIME < 21 then ScoreTime = "<= 8:00PM";
if 22 =< SCORE_DATETIME < 22 then ScoreTime = "<= 9:00PM";
if 23 =< SCORE_DATETIME < 23 then ScoreTime = "<= 10:00PM";
if 24 =< SCORE_DATETIME < 24 then ScoreTime = "<= 11:00PM";
run;

Sample of the dataset.

[PaigeMiller]

Your code is treating SCORE_DATETIME as an integer between 0 and 24, but the screen capture shows SCORE_DATETIME as a SAS date/time value. Assuming that it is a SAS date/time value, you want to use the TIMEPART function to extract the time part of the time stamp, rounded down to hour.

data want;
    set have;
    time=floor(timepart(score_datetime)/3600);
run;

Alternatively, you could use the HOUR format, but I think it does a different rounding than you want, as illustrated here: https://documentation.sas.com/?cdcId=pgmmvacdc&cdcVersion=9.4&docsetId=leforinforref&docsetTarget=p1...

[aperansi]

It isnt necessary for me to treat the datetime as an integer, I am open to suggestions. What would I need to plug in to code to treat the 1-24 as a date time instead?

[PaigeMiller]

---

@aperansi wrote:
It isnt necessary for me to treat the datetime as an integer, I am open to suggestions.

---

Datetime values are numbers, regardless of how you want to treat them.

What would I need to plug in to code to treat the 1-24 as a date time instead?

I'm not sure what this means. What 1-24 are you referring to?

[ballardw]

First I might suggest looking at https://communities.sas.com/t5/SAS-Communities-Library/Working-with-Dates-and-Times-in-SAS-Tutorial/... has a PDF with much information about dates and datetime values.

The likelihood of your Score_datetime =0 approaches zero. That would mean that the value was actually related to  01JAN60:00:00:00

and you are completely ignoring all values between 11PM and midnight.

I would suggest that if you actually need another variable (may not be the case) that you use a simple:

scoretime = Hour(score_datetime);

Which will yield a numeric value from 0 to 23 and use a format to display the desired text for the values.

Or for a specific procedure you could assign one of the formats HHMM2. or HOUR2. (slightly different range results) to the score_datetime variable.

Example:

proc print data=work.timeday6;
   var score_datetime;
   format score_datetime hhmm2.;
run;

Groups created with a format are honored by almost all analysis, reporting and graphics procedures. So you can use the datetime value directly with the format to create reports by hour of day for example without actually adding variables.

I would strongly recommend that you reconsider the values you are using. With a character value then in reports and such the data will attempt to sort into this order:

" <= 1:00AM";
"12:00AM";
"<= 10:00AM";
"<= 10:00PM";
"<= 11:00AM";
"<= 11:00PM";
"<= 12:00PM";
"<= 1:00PM";
"<= 2:00AM";
"<= 2:00PM";
"<= 3:00AM";
"<= 3:00PM";
"<= 4:00AM";
"<= 4:00PM";
"<= 5:00AM";
"<= 5:00PM";
"<= 6:00AM";
"<= 6:00PM";
"<= 7:00AM";
"<= 7:00PM";
"<= 8:00AM";
"<= 8:00PM";
"<= 9:00AM";
"<= 9:00PM";

which is pretty hard to work with.

Your code might work with most of the values with

if  1 =< hour (SCORE_DATETIME) < 2 then ScoreTime ...

but it appears that you may already have a ScoreTime variable