BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
SASAna
Quartz | Level 8

Hello,

 

I need some help in passing blank values in DATE fields of the end Oracle Table.

 

I am connectting to ORacle with direct pass through option and pulling some data and mapping them to insert into another oracle table.

 

Proc Sql;
 Connect To Oracle(User=&Myid Orapw=&Mypwd Path="&Mydb");
      Create Table Test  as Select * From Connection to Oracle
      (select  -- 400 attributes--

        NULL as paid_dt_orig

  )

Quit;

 

 

Proc contents on Test table -

 

 281    PAID_DT_ORIG                    Char      1    $.             $.             PAID_DT_ORIG       

 

Inserting  this data into another Oracle table using libname statement.

 

      PROC SQL;
        INSERT INTO DBMS.&MyTable
        (&colnames)
        SELECT &colnames_a
        FROM  Test  A;
             %let NQ_COUNT=&SQLOBS;
        QUIT;

        %put &NQ_COUNT;

 

I am getting the below error  for all the DATEf ields where i have mapped NULL.

 

ERROR: Value 17 on the SELECT clause does not match the data type of the corresponding column listed after the INSERT table name.

 

Data is very huge. So  I can not do SAS formattings. IS there any easier way to update Proc SQL to create a blank date field?

 

Thanks,

Ana

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
SuryaKiran
Meteorite | Level 14

Need more information on how your adding the null values and what datatypes are your SAS tables and Oracle tables.

 

If your trying to insert into a oracle table with one column all null values then use PROC APPEND. For example if your oracle table has 3 variables and your SAS has two variables then when you append the SAS table to Oracle table with only 2 variables then null values are automatically inserted into the missing variable.

Thanks,
Suryakiran

View solution in original post

4 REPLIES 4
SuryaKiran
Meteorite | Level 14

Need more information on how your adding the null values and what datatypes are your SAS tables and Oracle tables.

 

If your trying to insert into a oracle table with one column all null values then use PROC APPEND. For example if your oracle table has 3 variables and your SAS has two variables then when you append the SAS table to Oracle table with only 2 variables then null values are automatically inserted into the missing variable.

Thanks,
Suryakiran
SASAna
Quartz | Level 8
Thanks Kiran. PROC APPEND worked fine and inserted the records without any error.

Thanks,
Ana
Tom
Super User Tom
Super User

So you created a variable in your ORACLE code

NULL as paid_dt_orig

that has all null values and NO TYPE.

It looks like this caused SAS to make the rational decision that it should map that as a character variable of length 1.

 

If you later try to load this character variable into an actual DATE variable in Oracle you will have a problem.

 

Can't you use a CAST() function in the pass thru Oracle code to create a date variable instead?

Or an INPUT() function on the SAS side to convert the character string to a date before trying to upload it back into Oracle?

 

 

SASAna
Quartz | Level 8
Hi Tom, Data is very huge 800 million records, so i was going for easier way that formatting the data in SAS after the ORacle data pull. I will test CAST().

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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
  • 4 replies
  • 2562 views
  • 0 likes
  • 3 in conversation