topic Re: How to convert hh:mm:ss from a SAS dataset to an Oracle Table in SAS Enterprise Guide

How to convert hh:mm:ss from a SAS dataset to an Oracle Table

alepage — Mon, 19 Feb 2024 21:28:38 GMT

Hello,

In my SAS dataset, the content of the variable HRDEBTRA look like : 14:50:05

Then if the Oracle table does not exist, I am using a proc append

proc append base=&libref..&table_name data=&table_name;
run;

where &libref is the name of the oracle schema, and table_name is the table name (target table)

work.&table_name (source table)

if the Oracle table exist the we update the table using the script below:

options sastrace=',,;d' sastraceloc=saslog nostsuffix; 
proc sql;
connect to oracle (AUTHDOMAIN=ORACLE_&OWNER._&Database. PATH=&Database. readbuff=1000);
execute(
MERGE INTO &schema..&TblName1. pt 
USING &SCHEMA..&TblName2. ps 
ON    (pt.recordId = ps.recordId) 
WHEN NOT MATCHED THEN INSERT VALUES
(&varlist1.)
WHEN MATCHED THEN UPDATE 
  SET 
  &varlist2.
) BY ORACLE;
DISCONNECT FROM ORACLE;
quit;

However, the variable HRDEBTRA in the Oracle table is like a date.

What's the best way to convert that funzy date to HH24:mm:ss ex: 14:50:05

2/1/2024 2:50:05 PM

Re: How to convert hh:mm:ss from a SAS dataset to an Oracle Table

Patrick — Mon, 19 Feb 2024 23:07:29 GMT

"In my SAS dataset, the content of the variable HRDEBTRA look like : 14:50:05"

SAS stores time and datetime values in a numerical variable as count of seconds. It's then the format attached to the variable that determines how this count of second gets displayed.

It looks like your variable only stores a time value - which is just a count of seconds.

In Oracle you normally store such data as a datetime value for which there are two data types: datetime and timestamp.

The easiest way to prepare your SAS data for loading into Oracle would be to convert your SAS Time value to a SAS DateTime value. That's not hard to do IF you know the calendar date that would match the time portion you've already got.

On the SAS side: Could you convert 14:50:05 to something like 02Jan2024:14:50:05 ? Do you have this date information somewhere in your data?

Re: How to convert hh:mm:ss from a SAS dataset to an Oracle Table

alepage — Tue, 20 Feb 2024 14:27:20 GMT

No, 2/1/2024 , I dont' know where does come from this date ?

Re: How to convert hh:mm:ss from a SAS dataset to an Oracle Table

Sajid01 — Tue, 20 Feb 2024 18:59:36 GMT

Hello @alepage
Oracle date is actually datetime value. There is no specific time variable AFAIK in Oracle.
If it is required to store only time values OR if the variable holds only time, then the appropriate approach would be to either convert the time to a number (for example number of seconds since midnight- the default SAS storage for time) OR to save time as a character string (thus 14:50:05 would be '14:50:05') and pass the string to ORACLE.

Re: How to convert hh:mm:ss from a SAS dataset to an Oracle Table

SASKiwi — Tue, 20 Feb 2024 19:10:22 GMT

@alepage - If you are required to update an Oracle datatime column, then you have no choice but to add a date to your time value. If you don't know what date needs to be supplied then ask your Oracle administrator or someone knowledgeable about the table contents for advice.