- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
I tried a lot to insert sas date value into oracle date variable, but always giving ERROR: Oracle execute error: ORA-0140: Cannot insert NULL into oracle table DATE field.
Below is my code:
Proc sql;
Create table A as Select "28MAR2023" As src_dt from tmp;
Proc sql;
Create table B as Select src_dt format=date9. from tmp;
quit;
Proc append data=B
Base=db1.tbl(sasdatefmt=(src_dt=date9.)) ;
Run;
Getting an error and code trying insert NULL value. Please suggest me how can I insert date value to oracle table date column.
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Your first CREATE statement is make a CHARACTER variable.
Your second CREATE statement is assuming that the variable SRC_DT in the TMP dataset is NUMERIC and has value are the number of days since 01JAN1960. Otherwise you could not attach the numeric format DATE9. to the variable and have it print anything reasonable.
Errors about NULL values sounds like perhaps the table in Oracle is defined with a NOT NULL constraint. Do you have any missing values in the dataset B? For which variables? Does dataset B have ALL of the variable that exist in the Oracle table TB1? Otherwise SAS will set those values to NULL, which might be the cause of the NULL value message.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Create table A as Select dhms("28MAR2023"d, 0, 0, 0) As src_dt format=datetime20. from tmp;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Proc sql;
Create table A as Select "28MAR2023" As src_dt from tmp;
Proc sql;
Create table B as Select dhms(src_dt,0,0,0) as src_dt format=datetime20. from tmp;
quit;
Proc append data=B
Base=db1.tbl(sasdatefmt=(src_dt=datetime20.)) ;
Run;
In this case also same issue, I am not able to resolve.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Your first CREATE statement is make a CHARACTER variable.
Your second CREATE statement is assuming that the variable SRC_DT in the TMP dataset is NUMERIC and has value are the number of days since 01JAN1960. Otherwise you could not attach the numeric format DATE9. to the variable and have it print anything reasonable.
Errors about NULL values sounds like perhaps the table in Oracle is defined with a NOT NULL constraint. Do you have any missing values in the dataset B? For which variables? Does dataset B have ALL of the variable that exist in the Oracle table TB1? Otherwise SAS will set those values to NULL, which might be the cause of the NULL value message.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content