Trying to change a numeric field to a SAS time value

[lauren2]

Hi Everyone,

I have a time variable in one of my datasets and I am trying to store it as a SAS time value. 

In the dataset, the time variable is numeric. Example data: 114634.0766435

What I am trying to capture from that field is 11:46:34 as that represents the Hours(military time), mins and seconds, and then changing it to a SAS time value so I can do time functions on it.

I am able to first change it to a character using the code below:

TO_CHAR(TIME)

But I am stuck from here.

Any help would be appreciated.

Thank you.

[PaigeMiller · Accepted solution]

TO_CHAR(TIME)

Are you using PROC SQL with a passthru to Oracle or some other database?

If not, in a SAS data step, you can use the SAS function HMS(), as follows:

h=floor(time/10000);
m=floor((time-h*10000)/100);
s=mod(floor(time),100);
time_value = hms(h,m,s);

format time_value time8.;

[lauren2]

@PaigeMiller 

Thanks so much! That worked.  Yes I was using a proc sql with a passthru to oracle, but wrote the SAS step instead. One more question if you have the time. I now have one date field in format date9. and one time field in format time8.

Can I combine these two variables into one and make it a date time field?

[ballardw]

---

@lauren2 wrote:

@PaigeMiller 

Thanks so much! That worked.  Yes I was using a proc sql with a passthru to oracle, but wrote the SAS step instead. One more question if you have the time. I now have one date field in format date9. and one time field in format time8.

Can I combine these two variables into one and make it a date time field?

---

You can use the DHMS function in SAS to create a SAS datetime field but is that what you want?

dt = dhms(date,0,0,time); where DATE is a valid SAS date valued variable and Time is a SAS time valued variable. The format takes date, hour, minute and second parameters (hence the function name) and time values in SAS are numbers of seconds, so you use zero for the hour and minute components)

Or is this again pass-through to Oracle (where I haven't a clue)

[lauren2]

@ballardw   I can use a SAS step or pass through to Oracle, but I should use SAS since SQL can only handle date time fields. Let me start from the beginning. In my dataset, I have a date variable that is stored as a number and it looks like 20200728. So the date is written in YYYYMMDD, but it is not stored as a SAS date value. I also have a time variable that is also stored as a number and it looks like 114634.07664, but it is not stored as a SAS time value. What I want from that field is 11:46:34. My goal, is to combine both of these fields into a SAS date time value. From there, I can change the format to whatever I need.

[PaigeMiller]

---

@lauren2 wrote:

@PaigeMiller 

Thanks so much! That worked.  Yes I was using a proc sql with a passthru to oracle, but wrote the SAS step instead. One more question if you have the time. I now have one date field in format date9. and one time field in format time8.

Can I combine these two variables into one and make it a date time field?

---

Yes, you use the DHMS() function.

[Kurt_Bremser]

You can use a custom picture format to make the conversion shorter:

proc format;
picture dbtime
  low-high = '99:99:99'
;
run;

data want;
set have;
sastime = input(put(numtime,dbtime.),time8.) + mod(numtime,1);
format sastime time16.7;
run;