Troubleshooting Proc Import Warning messages

[sonic_poem]

I have an Excel spreadsheet titled "Need" that includes a variable "Story."  The variable "Story" has a large text and is >$20,000 in length. Importing the spreadsheet "Need" with the variable "Story" using the proc import statement below is giving me error messages for every line in the dataset (see Warning message below.)  How do I troubleshoot and make sure all my variables are imported properly?

proc import OUT=want

DATAFILE= "C:File\Need.xlsx"

DBMS=XLSX REPLACE;

sheet='Sheet1';

run;

.

.

WARNING: Some character data was lost during transcoding in column: Story at obs 8540.

WARNING: Some character data was lost during transcoding in column: Story at obs 8542.

[ballardw · Accepted solution]

Proc import uses a very small number of rows to guess as to variable properties. So if the longest value of a variable does not occur the first 20 rows or so the guessed length will result in truncation. Also the maximum length of a SAS Character variable is a bit more than 32000 characters. If your text is longer than that it will be truncated.

Transcoding is usually related to characters that may involve national language characters and depending on the source character set and your current session settings some characters simply cannot be converted into something your SAS session uses. Sometimes this can be addressed by using different start up options or options describing the source file such as encoding.

You might have better luck saving the XLSX file as a CSV format text file as there are a few more options in Proc Import, such as guessingrows=max that allow better "guesses".

You should also be aware that you have not shown any Error. Those are Warnings. Which means data is imported just some values may not be as expected. An error would mean that no data set was created.

[sonic_poem]

Thanks for the info.  Will give csv file a chance.  Updated the subject title from "error" to "warning" messages.