BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Thylacine
Fluorite | Level 6

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Thylacine
Fluorite | Level 6
Datetime with SASDATEFMT did the work. Thanks.

View solution in original post

5 REPLIES 5
kiranv_
Rhodochrosite | Level 12

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

 

Patrick
Opal | Level 21

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

Thylacine
Fluorite | Level 6

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.

Patrick
Opal | Level 21

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

 

Thylacine
Fluorite | Level 6
Datetime with SASDATEFMT did the work. Thanks.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

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