topic Re: Appending to oracle. in SAS Data Management

Appending to oracle.

Thylacine — Wed, 28 Jun 2017 19:02:25 GMT

hi,

I encountered a problem. I have a data set in work lib with a column that needs to be current date. When i try to proc append the dataset to an existing oracle table it works for every other column exept for the one with date(column is empty). Column in oracle is data type and is holding sysdate(the oracle one) format data. I tried in many ways like datetime(),'put'ing with different formats, creating custom format in sas that is a copy of oracle sysdate mm/dd/yyyy hh:mm:ss am/pm (%p in picture) but can't get the row to append. Any suggestions?

Re: Appending to oracle.

kiranv_ — Wed, 28 Jun 2017 19:07:12 GMT

try using SASDATEFMT. We use SASDATEFMT extensively while doing appends

Please check the link below for the same.

http://support.sas.com/documentation/cdl/en/acreldb/63647/HTML/default/viewer.htm#a001371624.htm

Re: Appending to oracle.

Patrick — Wed, 28 Jun 2017 21:37:27 GMT

@Thylacine

SAS doesn't have a data type of DATE but SAS dates are stored in a variable of data type numeric. The only way for the SAS Access engine to detect that such a numeric SAS variable needs a date conversion when loading into Oracle is via a SAS date or datetime format applied (or via explicit instruction via SASDATEFMT).

I've made the experience that the SAS/Access engines doesn't recognize all SAS date and datetime formats and though can treat SAS date/datetime values like any other numerical values when loading into Oracle. For this reason I tend to use formats DATE9. and DATETIME21. for all my SAS variables with date/datetime values as with these formats implicit conversion always worked for me and never caused me any problems.

Re: Appending to oracle.

Thylacine — Thu, 29 Jun 2017 08:40:50 GMT

Well i tried both and failed. In latest version column is created by put(datetime(),datetime21.2) as inserted_at (tried 21. too), and append looks like proc append base=oracletable(sasdatefmt=(inserted_at='datetime21.2')) data=dataset force ; run; Doing something wrong or any more suggestions? Oracle column is in windows format h:mm:ss AMPM.

Re: Appending to oracle.

Patrick — Thu, 29 Jun 2017 10:30:32 GMT

@Thylacine

If your target column in Oracle is of type DATE then your SAS column must be of type NUMERIC with a format of DATE or DATETIME applied.

Don't confuse the format of a variable with its datatype!

A put(datetime(),datetime21.2) creates a character string and you would use this to load into an Oracle CHAR or VARCHAR column.

The following code should work once you've replaced the <libref>.<tablename> with our real Oracle table name.

data source;
   format inserted_at datetime21.;
   /* map all the columns from target */
   if 0 then set ora.oracletable;
   inserted_at=datetime();
run;

proc append base=ora.oracletable data=work.source force ;
run;

Re: Appending to oracle.

Thylacine — Mon, 03 Jul 2017 20:10:43 GMT

Datetime with SASDATEFMT did the work. Thanks.