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
proc sql;
create table events as
select input(translate(event_time,':',"."),hhmmss4.) format time5. as event_time_converted
from schedules;
quit
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.
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.);
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 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.
Ready to level-up your skills? Choose your own adventure.