How to configure variables format null to insert in Oracle

[Jr_Monteiro]

Hello guys!

I have a question and I need your help.

I have a PROC IMPORT of a worksheet in Excel that may or may not have records in all columns. After importing this data, I make some treatments and I need to insert the records into an Oracle table.

As the records after import assume any format, I need format my variables for after insert in Oracle, but as some variables are null I received this error message: "The character expression requires a character format".

Would anyone have a solution for how much do I need to format columns that may or may not contain data to insert into an Oracle table?

 This is part of my cod SAS:

PROC SQL;

INSERT INTO ORACLE.PROD_TABLE
SELECT COD_CUPOM_DESCONTO,
COD_CAMPANHA_DESCONTO,
NME_CUPOM_DESCONTO,
NUM_CPF_CNPJ_SEGDO FORMAT $CHAR18. ,
NUM_CEP_INI FORMAT 9.,
NUM_CEP_FIM FORMAT 9.,
SIG_FABRIC FORMAT $CHAR5.,
COD_TIPO_CONTR FORMAT 4.,
SIG_TIPO_DOC_COBR FORMAT $CHAR6.
FROM WORK.INSERIR_ORACLE; 
QUIT;

[PGStats]

Check the variable types in WORK.INSERIR_ORACLE. If COD_TIPO_CONTR for example is character instead of numeric, then use

input(COD_TIPO_CONTR, ? best.) as COD_TIPO_CONTR format=4.,

[Jr_Monteiro]

The problem is that the source is an import and when the column is filled in Excel the Guide assimilates by the fill type, otherwise it assumes the char data type.

For this moment your code would work fine, as it is as mine was before, but when the variable is filled in the file SAS will probably inform that I am trying to convert a number that is already number. As this error:

ERROR: INPUT function requires a character argument.
ERROR: Character expression requires a character format.

[PGStats · Accepted solution]

Sometimes, giving an explicit number format to your Excel columns will fix the problem. Otherwise you may try

input(cats(COD_TIPO_CONTR), ? best.) as COD_TIPO_CONTR format=4.,

[Jr_Monteiro]

Thank you so much! 

This code help me for input in Oracle database. My big problem is that sometimes a variable can be populated in Excel and sometimes not, it directly interferes with the type of imported data. Using only the format and put / input was not enough, because when something did not come as expected, type hoping to convert a string to a number, but the variable was already filled with a number had a conversion error.

But the CATS command helped me in this, I did not know it. Thanks a lot for the help!

[Reeza]

Hello ladies!

TLDR; In essence the issue you're encountering has little to do with nulls and more to due with a mismatch in variable types. You need to check the variable type are what Oracle expects. DateTimes are a little different so if your question concerns those, please post back. 

If this is a more than one time procedure you'll need to build a more robust method. 

This is because Excel files don't enforce types in columns, but databases and SAS do. 

EG may have a way to enforce types as you import them in your procedure, but you need to make sure that's occurring. For example, PROC IMPORT does not provide an option to make sure the variables are character or numeric while importing the data. So you cannot guarantee that the type will be the same in each run. You are more likely to encounter this when your data set has nulls, because of how SAS imports data. It goes through and has to guess at a type, but the number of records it scans before guessing a type may be too low. You can look at the GUESSINGROWS system option, but that's more complex for an Excel file.   

So you need to build a step into your process that will ensure the data types are correct. You may also be mixing up types and formats/informats. 

Type - controls how variable is stored in the data set; SAS has two types, character and numeric. 

Format - controls how the variable is displayed.

Informat - controls how the variable is read in.  

SAS doesn't allow type conversion on the fly, so you need to create new variables that have the type and format you need. You can do this within the PROC SQL step.

To convert to a character:

PUT(variableName, formatOutput)

To convert toa number:

INPUT(variableName, formatInput)

ie convert a number to a character variable within your SQL step:

put(NUM_CPF_CNPJ_SEGDO, $char18.) as NUM_CPF_CNPG_SEGDO_n FORMAT $CHAR18. ,
..,
input(cod_TIP_CONTR, 8.) as COD_TIP_CONTR_c FORMAT 8.,