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 2024

Innovate_SAS_Blue.png

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

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.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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