DATA Step, Macro, Functions and more

Hour 0 Qtr_Hr1 =12:15 AM

Reply
Occasional Contributor
Posts: 5

Hour 0 Qtr_Hr1 =12:15 AM

[ Edited ]

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;

Super User
Posts: 17,748

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/

 

 

PROC Star
Posts: 7,356

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
Posts: 17,748

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

 

 

Occasional Contributor
Posts: 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
Posts: 17,748

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.;
Occasional Contributor
Posts: 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 

Respected Advisor
Posts: 3,887

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
Posts: 10,466

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.

Respected Advisor
Posts: 4,640

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
Ask a Question
Discussion stats
  • 9 replies
  • 166 views
  • 0 likes
  • 6 in conversation