Calcite | Level 5

## Hour 0 Qtr_Hr1 =12:15 AM

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;

9 REPLIES 9
Super User

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

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/

Opal | Level 21

## Re: Hour 0 Qtr_Hr1 =12:15 AM

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

Super User

## Re: Hour 0 Qtr_Hr1 =12:15 AM

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#n1oc359bvmq...

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

Calcite | Level 5

## Re: Hour 0 Qtr_Hr1 =12:15 AM

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

Super User

## Re: Hour 0 Qtr_Hr1 =12:15 AM

@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.;``
Calcite | Level 5

## Re: Hour 0 Qtr_Hr1 =12:15 AM

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

Opal | Level 21

## Re: Hour 0 Qtr_Hr1 =12:15 AM

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

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

Super User

## Re: Hour 0 Qtr_Hr1 =12:15 AM

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

or

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.

Opal | Level 21

## Re: Hour 0 Qtr_Hr1 =12:15 AM

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;``````
PG
Discussion stats
• 9 replies
• 1401 views
• 0 likes
• 6 in conversation