topic Re: PROC SQL Oracle pass through - passing null values in date fields in SAS Programming

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

SASAna — Mon, 23 Apr 2018 15:46:24 GMT

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

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

SuryaKiran — Mon, 23 Apr 2018 16:25:24 GMT

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.

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

Tom — Mon, 23 Apr 2018 17:23:10 GMT

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?

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

SASAna — Mon, 23 Apr 2018 18:45:08 GMT

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

Thanks,
Ana

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

SASAna — Mon, 23 Apr 2018 18:48:03 GMT

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