topic Re: Format time in grouped blocks in SAS Programming

Format time in grouped blocks

lydiawawa — Mon, 17 Jun 2019 18:16:04 GMT

Hi All,

I have a time variable in the format of datetime32.4(ex: 21MAR2019:10:19:15.2970) and I'm trying to group the time into the following block such as showing in the SAS code:

 proc format;
value timerng
"06:00"t -< "15:59"t= "6:00am - 3:59pm"
"16:00"t -< "21:59"t = "4:00pm - 9:59pm"
"22:00"t -< "05:59"t = "10:00pm - 5:59am";
run;

I received an error message saying the start is greater than the end. Is there any other way to group the time variable into the wanted blocks?

Thank you!

Re: Format time in grouped blocks

PaigeMiller — Mon, 17 Jun 2019 18:24:04 GMT

"22:00"t -< "05:59"t = "10:00pm - 5:59am"

This is the problem, you can't have 05:59 is the high end of the range because it is less than 22:00

Perhaps what you want is:

proc format;
     ...
    "22:00"t-"23:59"t, "00:00"t-"05:59"t = "10:00 pm - 5:59 am";
run;

Re: Format time in grouped blocks

lydiawawa — Mon, 17 Jun 2019 18:33:22 GMT

The range worked, but instead of printing in the grouped value ("6:00am - 3:59pm"..etc). The output looks like this:
"1837409536.698"

Re: Format time in grouped blocks

PaigeMiller — Mon, 17 Jun 2019 18:43:05 GMT

Show us the code.

Show us sample data.

Re: Format time in grouped blocks

Reeza — Mon, 17 Jun 2019 18:58:46 GMT

You have a date time variable but created a time format. You'll need to separate the two first using TIMEPART to have your time correctly set up. Otherwise, especially if you're trying to create groups across dates you'll need a different approach. And do you need any custom calculations for daylight savings time.

Re: Format time in grouped blocks

lydiawawa — Mon, 17 Jun 2019 19:20:33 GMT

Thank you for the hint. Timepart() worked!

Re: Format time in grouped blocks

lydiawawa — Wed, 03 Jul 2019 20:05:29 GMT

For some reason the following format does not cover the end point:

proc format;
value timerng
"06:00"t - "15:59"t= "6:00am - 3:59pm: Daytime"
"16:00"t - "21:59"t= "4:00pm - 9:59pm: Evening"
"22:00"t - "23:59"t, "00:00"t - "05:59"t, = "10:00pm - 5:59am: Overnight";
run;

When I do a freq count entries with time equal to 15:59 or 21:59 are all not converted to the categorical group. Does anyone know what is the cause?

This is what I have in return for 9:59pm: 7919.5840001106

Re: Format time in grouped blocks

Reeza — Wed, 03 Jul 2019 20:16:27 GMT

That's because you have seconds so 9:59.45 seconds will not be in those intervals. Just put them at the start of the next interval and SAS will sort it out, or use the -< explicitly exclude and include your endpoints.

"06:00"t - < "16:00"t = ......

http://support.sas.com/documentation/cdl/en/proc/65145/HTML/default/viewer.htm#n03qskwoints2an1ispy57plwrn9.htm

Re: Format time in grouped blocks

ScottBass — Wed, 03 Jul 2019 23:18:39 GMT

+1 for the -< operand in proc format

Re: Format time in grouped blocks

lydiawawa — Wed, 17 Jul 2019 20:25:55 GMT

Sorry this still did not work.. but I created a conditional statement after dividing time variable into min and second.

Re: Format time in grouped blocks

Reeza — Wed, 17 Jul 2019 21:07:39 GMT

Didn't work is vague and could mean anything from doesn't tell us if your computer disintegrated into bits or if you forgot a semicolon.

You need to provide more information, specifically the code you ran, the log and what isn't working.

@lydiawawa wrote:
Sorry this still did not work.. but I created a conditional statement after dividing time variable into min and second.

Re: Format time in grouped blocks

ScottBass — Thu, 18 Jul 2019 00:02:17 GMT

@Reeza wrote:

Didn't work is vague and could mean anything from doesn't tell us if your computer disintegrated into bits or if you forgot a semicolon.

You need to provide more information, specifically the code you ran, the log and what isn't working.

Classic reply @Reeza 🙂 🙂 🙂 Couldn't stop laughing. Plus it's spot on (but still funny). Wish I could give this 10 likes.

Re: Format time in grouped blocks

Reeza — Thu, 18 Jul 2019 01:39:32 GMT

It's not intended to be mean, and I hope it doesn't come across that way, just that it's such a vague statement that conveys no information.

Re: Format time in grouped blocks

ScottBass — Thu, 18 Jul 2019 01:56:30 GMT

@Reeza wrote:
It's not intended to be mean, and I hope it doesn't come across that way, just that it's such a vague statement that conveys no information.

No it didn't come across as mean to me, and I hope not to @lydiawawa as well. I just thought your examples of vagueness, such as the computer disintegrating to bits, as funny. Perhaps that's just my sense of humor.

I wonder how you'd post an example of that? Take a picture and attach it I suppose 😉

Re: Format time in grouped blocks

lydiawawa — Thu, 18 Jul 2019 02:29:11 GMT

I just got back from work. Really didn't expect this post can be this controversial lol. Anyways, the dataset I'm dealing with is so large and it is para data. There are no errors in log, no missing semicolons. It might be the dataset is just too messy to be formatted? Thank you for the help!

Re: Format time in grouped blocks

ScottBass — Thu, 18 Jul 2019 02:31:20 GMT

You need to provide more information, specifically the code you ran, the log and what isn't working.

What part of this is unclear?

Re: Format time in grouped blocks

Reeza — Thu, 18 Jul 2019 02:33:45 GMT

Code can be wrong via syntax, which the log picks up, or wrong as in logically wrong, where it doesn't do what you think it's doing or want it to do. I'm guessing you're in the latter but without the code we can't help you any further. It's your choice on how to proceed.

Re: Format time in grouped blocks

lydiawawa — Thu, 18 Jul 2019 02:38:29 GMT

proc format;
value timerng
"06:00"t -< "15:59"t= "6:00am - 3:59pm: Daytime"
"16:00"t -< "21:59"t= "4:00pm - 9:59pm: Evening"
"22:00"t -< "23:59"t, "00:00"t -< "05:59"t, = "10:00pm - 5:59am: Overnight";
run;

I added "<" next to all the slashes, but the same problem persisted.

Re: Format time in grouped blocks

Reeza — Thu, 18 Jul 2019 02:46:52 GMT

You never fixed the intervals. See my earlier response, which may not have been that clear, also below. Notice that ending goes to 00, not to 59. When it goes to 59, values such as 12:59.45 don't get grouped anywhere because it's not included in any of your ranges. So you need to change the ends to be the same as the beginning of the next interval AND use the exclude option to exclude it. Or put them the same and let SAS figure it out, I think the default is what you want. See the section in red below.

Re: Format time in grouped blocks

Posted in reply to lydiawawa

‎2019-07-03 04:16 PM

That's because you have seconds so 9:59.45 seconds will not be in those intervals. Just put them at the start of the next interval and SAS will sort it out, or use the -< explicitly exclude and include your endpoints.

"06:00"t - < "16:00"t = ......

http://support.sas.com/documentation/cdl/en/proc/65145/HTML/default/viewer.htm#n03qskwoints2an1ispy5...

Re: Format time in grouped blocks

ScottBass — Thu, 18 Jul 2019 02:47:10 GMT

Review this and tell us which observation numbers are incorrect:

data have;
   do time="00:00:00"t to "23:59:59"t;
      output;
   end;
   format time time.;
run;

proc format;
   value timerng
      "06:00:00"t -< "16:00:00"t  = "6:00am - 3:59pm: Daytime"
      "16:00:00"t -< "22:00:00"t  = "4:00pm - 9:59pm: Evening"
      "22:00:00"t -  "23:59:59"t, 
      "00:00:00"t -< "06:00:00"t  = "10:00pm - 5:59am: Overnight";
run;

data want;
   set have;
   time2=put(time,timerng.);
run;