DATA Step, Macro, Functions and more

SAS Base Date insert into Oracle Date

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 10
Accepted Solution

SAS Base Date insert into Oracle Date

Hello,

 

i want to insert a SAS Date from a SAS Table into a Oracle Date-column.

 

I searched already in Google and in this forum - but i get always:

 

ERROR: Value 7 on the SELECT clause does not match the data type of the corresponding column listed after the INSERT table name.

 

The Oracle table looks like:

 

CREATE TABLE "TEST_TABLE"
   (   
    "REPORT_CREATION" DATE
   )

 

 

The date-column "mdupdate" in the SAS Table is "alphanumeric 20" and it is shown: "29Jun2016:14:55:56"

 

One example i tried is:

 

PROC SQL;

    INSERT INTO
        ORADB.TEST_TABLE (REPORT_CREATION)
    SELECT
        put(mdupdate,datetime22.)
    from
        SB0520C.REPORTS;

QUIT;

 

or i tried it with:

mdupdate format=datetime22.

 

But i get always an error.

 

How must i format the date correct?

 

Can someone help please?

 

Thank you very much

Best regards

George

 

 

 


Accepted Solutions
Solution
‎09-25-2016 09:04 AM
Super User
Posts: 17,819

Re: SAS Base Date insert into Oracle Date

SAS has numeric and character variables. It sounds like you have a char variable. 

Oracle will want a numeric variable with a datetime format. 

 

Try input instead of put, which will convert your char variable to a numeric variable. 

 

Input(mdupdate, datetime20.) format = datetime20.

 

View solution in original post


All Replies
Solution
‎09-25-2016 09:04 AM
Super User
Posts: 17,819

Re: SAS Base Date insert into Oracle Date

SAS has numeric and character variables. It sounds like you have a char variable. 

Oracle will want a numeric variable with a datetime format. 

 

Try input instead of put, which will convert your char variable to a numeric variable. 

 

Input(mdupdate, datetime20.) format = datetime20.

 

Occasional Contributor
Posts: 10

Re: SAS Base Date insert into Oracle Date

Life could be so easy....)

Thank you very much Reeza.

Input(mdupdate, datetime20.) format = datetime20. --> this was the solution!

Thank you very much for your very fast help! Great work!

Best regards
George
Respected Advisor
Posts: 3,889

Re: SAS Base Date insert into Oracle Date

The SAS Access to Oracle Engine communicates with the Oracle client to communicate with your Oracle server.

 

In doing so the Access Engine must know of which data type a SAS variable should become for Oracle. Now standard SAS has only 2 variable types - numeric or character. So for SAS to pass on the correct type to Oracle (Date), SAS needs to know that the SAS variable of type Numeric represents a SAS Date or Datetime. The way SAS gets that is via the Format assigned to the numeric SAS variable - BUT: The SAS variable must remain numeric and not get converted to a string like done in your code.

 

IF you have already a permanend DATETIME. format assigned to variable "mdupdate" the you can just use it directly as below:

 

PROC SQL;
    INSERT INTO 
        ORADB.TEST_TABLE (REPORT_CREATION) 
    SELECT 
        mdupdate 
    from 
        SB0520C.REPORTS;
QUIT;

IF "mdupdate" doesn't have a permanent format applied then something like below should work:

PROC SQL;
    INSERT INTO 
        ORADB.TEST_TABLE (REPORT_CREATION) 
    SELECT 
        mdupdate format=datetime21.
    from 
        SB0520C.REPORTS;
QUIT;

What you also can do, is using data set option "dbtype" - but this should only be necessary in very special situations. http://support.sas.com/documentation/cdl/en/acreldb/69039/HTML/default/viewer.htm#p1dqaq7ub1cm1pn1ow...

 

BTW: I've made the experience that the SAS Access to Oracle engine doesn't recognize all SAS Date and Datetime formats (=eventually converting the SAS variable to an Oracle column of type numeric). If you want to stay on the save side then use DATE9. and DATETIME21.

 

 

 

☑ This topic is SOLVED.

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

Discussion stats
  • 3 replies
  • 975 views
  • 1 like
  • 3 in conversation