Solved: Re: Format time in grouped blocks

lydiawawa · Posted 06-17-2019 02:12 PM

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!

ScottBass · Posted 07-17-2019 10:47 PM

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;

Please post your question as a self-contained data step in the form of "have" (source) and "want" (desired results).
I won't contribute to your post if I can't cut-and-paste your syntactically correct code into SAS.

View solution in original post

PaigeMiller · Posted 06-17-2019 02:24 PM

"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;

--
Paige Miller

lydiawawa · Posted 06-17-2019 02:33 PM

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

PaigeMiller · Posted 06-17-2019 02:43 PM

Show us the code.

Show us sample data.

--
Paige Miller

Reeza · Posted 06-17-2019 02:58 PM

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.

lydiawawa · Posted 06-17-2019 03:20 PM

Thank you for the hint. Timepart() worked!

lydiawawa · Posted 07-03-2019 04:04 PM

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

Reeza · Posted 07-03-2019 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...

ScottBass · Posted 07-03-2019 07:18 PM

+1 for the -< operand in proc format

Please post your question as a self-contained data step in the form of "have" (source) and "want" (desired results).
I won't contribute to your post if I can't cut-and-paste your syntactically correct code into SAS.

lydiawawa · Posted 07-17-2019 04:25 PM

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

Reeza · Posted 07-17-2019 05:07 PM

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.

ScottBass · Posted 07-17-2019 08:02 PM

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

Please post your question as a self-contained data step in the form of "have" (source) and "want" (desired results).
I won't contribute to your post if I can't cut-and-paste your syntactically correct code into SAS.

Reeza · Posted 07-17-2019 09:39 PM

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.

ScottBass · Posted 07-17-2019 09:55 PM

@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 😉

Please post your question as a self-contained data step in the form of "have" (source) and "want" (desired results).
I won't contribute to your post if I can't cut-and-paste your syntactically correct code into SAS.

lydiawawa · Posted 07-17-2019 10:29 PM

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!

Ready to join fellow brilliant minds for the SAS Hackathon?

Classroom Training Available!