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
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.
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.
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.
Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.