BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Banu
Obsidian | Level 7
Hi Team,
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.
1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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.

View solution in original post

5 REPLIES 5
Reeza
Super User
Most DBs have datetime values, not just dates. Try creating a datetime variable instead of a date variable and inserting that.

Create table A as Select dhms("28MAR2023"d, 0, 0, 0) As src_dt format=datetime20. from tmp;


Banu
Obsidian | Level 7
I even tried below steps.
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.
Reeza
Super User
Note the d after the date in my code that isn't in yours. That tells SAS that the value is a date, not just text.
Tom
Super User Tom
Super User

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.

Banu
Obsidian | Level 7
I think you are right, need to verify the sas table columns and oracle table columns. And Yes src_dt is not null in my case. I will revisit my code and will get back you.

SAS Innovate 2025: Register Now

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!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 5 replies
  • 991 views
  • 0 likes
  • 3 in conversation