topic Re: Trying to change a numeric field to a SAS time value in SAS Programming

Trying to change a numeric field to a SAS time value

lauren2 — Tue, 25 Aug 2020 00:56:29 GMT

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.

Re: Trying to change a numeric field to a SAS time value

PaigeMiller — Tue, 25 Aug 2020 01:08:40 GMT

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

Re: Trying to change a numeric field to a SAS time value

lauren2 — Tue, 25 Aug 2020 03:15:02 GMT

@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?

Re: Trying to change a numeric field to a SAS time value

ballardw — Tue, 25 Aug 2020 07:59:00 GMT

@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)

Re: Trying to change a numeric field to a SAS time value

Kurt_Bremser — Tue, 25 Aug 2020 09:02:33 GMT

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;

Re: Trying to change a numeric field to a SAS time value

PaigeMiller — Tue, 25 Aug 2020 11:28:51 GMT

@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.

Re: Trying to change a numeric field to a SAS time value

lauren2 — Tue, 25 Aug 2020 16:26:01 GMT

@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.