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.png

 

Secure your spot at the must-attend AI and analytics event of 2024: SAS Innovate 2024! Get ready for a jam-packed agenda featuring workshops, super demos, breakout sessions, roundtables, inspiring keynotes and incredible networking events.

 

Register by March 1 to snag the Early Bird rate of just $695! Don't miss out on this exclusive offer. 

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

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

View all other training opportunities.

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