06-28-2017 03:02 PM
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?
06-28-2017 03:07 PM
try using SASDATEFMT. We use SASDATEFMT extensively while doing appends
Please check the link below for the same.
06-28-2017 05:35 PM - edited 06-28-2017 05:37 PM
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.
06-29-2017 04:40 AM - edited 06-29-2017 04:40 AM
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.
06-29-2017 06:29 AM - edited 06-29-2017 06:30 AM
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;
Need further help from the community? Please ask a new question.