DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 123
Accepted Solution

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

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

 

 

 

 


Accepted Solutions
Solution
a month ago
Super Contributor
Posts: 472

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

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


All Replies
Solution
a month ago
Super Contributor
Posts: 472

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

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
Frequent Contributor
Posts: 123

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

Posted in reply to SuryaKiran
Thanks Kiran. PROC APPEND worked fine and inserted the records without any error.

Thanks,
Ana
Super User
Super User
Posts: 7,858

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

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?

 

 

Frequent Contributor
Posts: 123

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

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().
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 113 views
  • 0 likes
  • 3 in conversation