BookmarkSubscribeRSS Feed
Scottie_T
Calcite | Level 5

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.

 

Time_Chart.jpg

 

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
Reeza
Super User

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/

 

 

art297
Opal | Level 21

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

 

Reeza
Super User

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

 

 

Scottie_T
Calcite | Level 5

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 

 

 

Reeza
Super User

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

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 

Patrick
Opal | Level 21

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

ballardw
Super User

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.

PGStats
Opal | Level 21

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 1193 views
  • 0 likes
  • 6 in conversation