SAS Base Date insert into Oracle Date

[sorosch]

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

[Reeza · Accepted solution]

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.

[sorosch]

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

[Patrick]

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.