topic Re: Change numeric variable of type xx.xx to a time variable in SAS Programming

Change numeric variable of type xx.xx to a time variable

Olscream — Fri, 12 Apr 2019 08:03:43 GMT

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

Re: Change numeric variable of type xx.xx to a time variable

Shmuel — Fri, 12 Apr 2019 08:11:19 GMT

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

Re: Change numeric variable of type xx.xx to a time variable

andreas_lds — Fri, 12 Apr 2019 08:15:37 GMT

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

Re: Change numeric variable of type xx.xx to a time variable

Olscream — Fri, 12 Apr 2019 08:20:31 GMT

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.

Re: Change numeric variable of type xx.xx to a time variable

Kurt_Bremser — Fri, 12 Apr 2019 08:43:53 GMT

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.