BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Jr_Monteiro
Fluorite | Level 6

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?

 

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;

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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.,

PG

View solution in original post

5 REPLIES 5
PGStats
Opal | Level 21

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.,

PG
Jr_Monteiro
Fluorite | Level 6

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
Opal | Level 21

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.,

PG
Jr_Monteiro
Fluorite | Level 6

Thank you so much! Man Happy

 

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
Super User

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.,

 

 

 

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 2524 views
  • 2 likes
  • 3 in conversation