topic Re: Hour 0 Qtr_Hr1 =12:15 AM in SAS Programming

Hour 0 Qtr_Hr1 =12:15 AM

Scottie_T — Thu, 12 Jan 2017 22:42:14 GMT

I currently have Loc_NO, Trns_Date, Hour, Qtr Hour.

How do I create a variable that reads hours 0 to 23 and Qtr_Hour 1 to 4 and outputs the appropriate time? (12:15 AM, etc)

Sample code is below the chart.

Proc SQL;
Create Table Work.Shipping_Qtr_Hour_Revenue AS
Select Loc_No,Trns_Date,Hour,Qtr_Hour,Revenue,
From Qtr_Hour_Revenue_All
Group By Loc_No,Trns_Date,Hour,Qtr_Hour
;
Quit;

Re: Hour 0 Qtr_Hr1 =12:15 AM you idiot!!

Reeza — Thu, 12 Jan 2017 22:40:55 GMT

I'm not sure who's an idiot but I don't understand your question.

Can you clarify with what you have and what you want? Also, please post data as text, not an image. With an image, if someone want's to provide code they need to not only write the code but write test data. In my books this would be mean I would have to be very interested in the question. There's even an EG task that will do it for you here.

http://blogs.sas.com/content/sasdummy/2013/01/21/turn-your-data-set-into-a-data-step-program/

Re: Hour 0 Qtr_Hr1 =12:15 AM

art297 — Thu, 12 Jan 2017 22:47:30 GMT

I'll guess that you're looking for something like:

Proc SQL;
Create Table Work.Shipping_Qtr_Hour_Revenue AS
Select Loc_No,Trns_Date,Hour,Qtr_Hour,Revenue,
input(catt(hour,':',(qtr_hour-1)*15),time5.) as time format=timeampm8.
From Qtr_Hour_Revenue_All
Group By Loc_No,Trns_Date,Hour,Qtr_Hour
;
Quit;

However, the above slightly deviates from your specs since it treats qtr_Hour 1 as the start and qtr_hour 4 as n:45.

HTH,

Art, CEO, AnalystFinder.com

Re: Hour 0 Qtr_Hr1 =12:15 AM

Reeza — Thu, 12 Jan 2017 22:48:03 GMT

I think you just want the time from hour and qtr_hour.

Time is measured in seconds so convert everything to the same unit and then display appropriately.

Or use HMS() function

http://support.sas.com/documentation/cdl/en/lefunctionsref/69762/HTML/default/viewer.htm#n1oc359bvmqub9n1iavw6u3we8oi.htm

These functions can be used in SQL or if using Query Builder then create a computed column.


data test;
input hour qtr_hour;

Time= hour*60*60+qtr_hour*15*60;
time2=hms(hour, qtr_hour*15, 0);
Format time: time8.;

cards;
0 1
0 2
0 3
0 4
1 1
23 2
15 3
;
run;

PS. 0 Hour and QTR 1 is not 12:15 it's 00:15

Re: Hour 0 Qtr_Hr1 =12:15 AM

Scottie_T — Thu, 12 Jan 2017 22:53:11 GMT

Reeza,

It was just me being goody with a header and I feel like an adiot with it... lol

Yes it will Read as 00:15 once it is programmed and I will replace it with 12:15 Am.

I was trying to format variables such as Hour=0 to read as a 12, Hour=1 to read as 1, Qtr_Hour=1 read as 15, qtr_hour=2 read as 30.

So just read the two columns and output the approporate time. Then an AM or PM. I think I have stared at it to long to really know the best way.

Thanks for all of your help!!

Scottie

Re: Hour 0 Qtr_Hr1 =12:15 AM

Reeza — Thu, 12 Jan 2017 22:56:31 GMT

@Scottie_T I did remove it because it didn't feel right. If you're set on it being there, feel free to add it.

Use the appropriate format instead of fighting with AM/PM, change to my code would be:

Format time: timeampm.;

Re: Hour 0 Qtr_Hr1 =12:15 AM

Scottie_T — Thu, 12 Jan 2017 23:04:02 GMT

Reeza & Art,

Thanks so much for your solutions I will work through these solutions and try them out.

I am new to programming so learning as fast as I can.

Thanks

Scottie

Re: Hour 0 Qtr_Hr1 =12:15 AM

ballardw — Thu, 12 Jan 2017 23:56:08 GMT

Time = hms(hour, qtr_hour*15,0); in a data step

hms(hour, qtr_hour*15,0) as Time in proc SQL

However you will want to assign a FORMAT to display the value as hh:MM such as TIME5.

Re: Hour 0 Qtr_Hr1 =12:15 AM

Patrick — Fri, 13 Jan 2017 00:03:50 GMT

@Scottie_T

If you're new to programming then make sure that you understand how SAS deals with dates and times and especially make sure that you understand the difference of how SAS stores Date and Time values internally and how it prints it using formats (and "print" applies also if you just look at your data in a table).

http://support.sas.com/documentation/cdl/en/lrcon/69852/HTML/default/viewer.htm#p1wj0wt2ebe2a0n1lv4lem9hdc0v.htm

Example: It would be possible that your quarter variable actually already stores a SAS time value and that there is just a format to the variable which then prints this time value as quarters. In such a case there wouldn't be any calculations required and you would just assign a different format. There are then also ways to use the formatted values for grouping/summarizations.

Above is the reason why most of the case people just store SAS Date, Time and DateTime values and then apply the format which best suits their needs (and if there isn't already an OOTB format then you can also create your own one using Proc Format / Picture ).

Re: Hour 0 Qtr_Hr1 =12:15 AM

PGStats — Fri, 13 Jan 2017 05:24:34 GMT

Build on this:

Proc SQL;
Create Table Work.Shipping_Qtr_Hour_Revenue AS
Select 
    Loc_No,
    Trns_Date,
    Hour,
    Qtr_Hour,
    hour*'01:00:00't + qtr_hour*'00:15:00't as time format=timeAMPM8.,
    Revenue,
From Qtr_Hour_Revenue_All
Group By Loc_No, Trns_Date, Hour, Qtr_Hour;
Quit;