SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Appending to oracle.

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 11
Accepted Solution

Appending to oracle.

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?


Accepted Solutions
Solution
‎07-03-2017 04:11 PM
Occasional Contributor
Posts: 11

Re: Appending to oracle.

Datetime with SASDATEFMT did the work. Thanks.

View solution in original post


All Replies
PROC Star
Posts: 253

Re: Appending to oracle.

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

 

Respected Advisor
Posts: 3,893

Re: Appending to oracle.

[ Edited ]

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

Occasional Contributor
Posts: 11

Re: Appending to oracle.

[ Edited ]

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.

Respected Advisor
Posts: 3,893

Re: Appending to oracle.

[ Edited ]

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

 

Solution
‎07-03-2017 04:11 PM
Occasional Contributor
Posts: 11

Re: Appending to oracle.

Datetime with SASDATEFMT did the work. Thanks.
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 268 views
  • 3 likes
  • 3 in conversation