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
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.
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.
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?
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.