BookmarkSubscribeRSS Feed
Olscream
Fluorite | Level 6

Hello everyone,

 

I'm trying to convert in a SQL procedure a numeric variable (which represents the time of an event as "15.30" for 15:30 or 12.45 such as 12:45) to a time one (like 15:30 or 12:45). But I'm not able to get the minutes, I can just get 15:00 when the numeric variable is 15.30 for example. Would you know how to fix it please ? Thank you in advance for your help.

 

The related query is as following :

 

 

proc sql;
create table events as
select input(put(event_time,3.3),hhmmss4.) format time5. as event_time_converted
from schedules;
quit

 

4 REPLIES 4
Shmuel
Garnet | Level 18
proc sql;
create table events as
select input(translate(event_time,':',"."),hhmmss4.) format time5. as event_time_converted
from schedules;
quit
Olscream
Fluorite | Level 6

Thanks ! But it doesn't perfectly work :

 

ERROR: Function TRANSLATE requires a character expression as argument 1.
ERROR: Numeric format F in PUT function requires a numeric argument.
andreas_lds
Jade | Level 19

Please try

input(cat(event_time), time.)

If you aren't forced to use proc sql for such task, switching to data step is recommended for easier debugging.

 

Sorry, should have looked closer at the result. Cat-function does not help here, we need put:

input(put(event_time, 5.2), time.);
Kurt_Bremser
Super User

When specifying numeric formats, keep this in mind:

The first number specifies the whole length, including digits before the comma, digits after the comma, the comma itself, and any other characters that the format will produce. The second number specifies only the fractional digits. So 3.3 can never work, as the overall length doesn't even leave space for the comma.

See where this code (which works) differs from yours:

data schedules;
input event_time;
cards;
15.30
12.45
;
run;

proc sql;
create table events as
select input(translate(put(event_time,z5.2),':','.'),time5.) format time5. as event_time_converted
from schedules;
quit;

The Zw.d format forces leading blanks. A length of 5 is needed for 2 digits + comma + 2 digits.

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 1700 views
  • 1 like
  • 4 in conversation