PROC SQL Oracle pass through - passing null values in date fields

[SASAna]

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

[SuryaKiran · Accepted solution]

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.

[SASAna]

Thanks Kiran. PROC APPEND worked fine and inserted the records without any error.

Thanks,
Ana

[Tom]

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]

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().