topic Re: WARNING: Some character data was lost during transcoding in column: in SAS Programming

WARNING: Some character data was lost during transcoding in column:

LL5 — Thu, 19 Nov 2020 18:06:20 GMT

Hi, I am getting a warning message of "Some character data was lost during transcoding in column" from the below codes.

I have two questions and any helps or advice are gladly appreciated.

1. Is there any way to fix the warning message?

2. It appears Sale_description is causing this issue but I don't even have this variable in the keep = statement. Is there any way to only read 4 variables from the dataset so we don't see the warning message (which is caused by sale_description)?

Thanks

%let a = /Inventory/group/sale_20200930.xlsx ;
libname sale xlsx "&a";


data inv_sept ; 
set sale.sale_sept (keep = sale_amount sale_target sale_location sale_person); 
run;

WARNING: Some character data was lost during transcoding in column: Sale_description at obs 332

...........

WARNING: Some character data was lost during transcoding in column: Sale_description at obs 998
NOTE: The import data set has 1284 observations and 25 variables.
NOTE: There were 1284 observations read from the data set SALE.SALE_SEPT
NOTE: The data set WORK.INV_SEPT has 1284 observations and 4 variables.
NOTE: DATA statement used (Total process time):
real time 0.60 seconds
cpu time 0.59 seconds

Re: WARNING: Some character data was lost during transcoding in column:

ballardw — Thu, 19 Nov 2020 19:04:30 GMT

If you aren't using the variable then ignore the warning.

If you need to have a clean "log" you could try making a copy of your source file without that column.

Or try the option Transcode_Fail = Silent on the libname statement.

Re: WARNING: Some character data was lost during transcoding in column:

LL5 — Thu, 19 Nov 2020 19:23:03 GMT

Thanks Ballardw, do you mind to show me how to make a copy of my source file without that column?

Re: WARNING: Some character data was lost during transcoding in column:

ballardw — Thu, 19 Nov 2020 20:14:21 GMT

@LL5 wrote:

Thanks Ballardw, do you mind to show me how to make a copy of my source file without that column?

Make a copy of the file using system tools.

Open the copy.

Highlight the column

Delete the column

Save the file

Point the SAS code to the new file.

Re: WARNING: Some character data was lost during transcoding in column:

LeonidBatkhan — Thu, 19 Nov 2020 20:22:04 GMT

Hi LL5,

To make sure you don't lose character data during transcoding, you need to expand your character lengths before transcoding occur. The simplest way of doing it is by using CVP (Character Variable Padding) special Engine which is part of SAS BASE. This technique is described in detail in my recent blog post Expanding lengths of all character variables in SAS data sets.

Hope this helps.

Re: WARNING: Some character data was lost during transcoding in column:

LL5 — Thu, 19 Nov 2020 20:41:49 GMT

Thanks Leonid, but since I am reading an external excel file by using xlsx libname here, would CVP work in this case? It appears to me that CVP engine applies to sas dataset only. It'd be great if you can share some more insights.

Re: WARNING: Some character data was lost during transcoding in column:

LL5 — Thu, 19 Nov 2020 20:44:08 GMT

Thanks again. This approach may not work in my work situation because there would be no human intervention since the the program runs automatically via a scheduled job, but appreciated for your advice.

Re: WARNING: Some character data was lost during transcoding in column:

LeonidBatkhan — Thu, 19 Nov 2020 20:59:30 GMT

I guess your SAS session has encoding WLATIN1, while your .xlsx file has UTF-8 encoding. You need to change your SAS session's encoding to UTF-8: How to Run a SAS Session in UTF-8 Encoding.

You can find out your SAS session encoding by running this code snippet:

proc options option=encoding;
run;

Hope this helps.

Re: WARNING: Some character data was lost during transcoding in column:

LL5 — Thu, 19 Nov 2020 21:25:31 GMT

My SAS session has encoding LATIN1. I tried to change the xlsx libname to proc import as below. I also added the encoding to the filename, but I am still getting the same warning message.

filename a '/Inventory/group/sale_20200930.xlsx’ encoding='LATIN1' ;
PROC IMPORT
datafile = a
out= inv_sept  (keep = sale_amount sale_target sale_location sale_person)
dbms=xlsx
replace;
sheet = "sale_sept";
RUN;

Re: WARNING: Some character data was lost during transcoding in column:

Tom — Thu, 19 Nov 2020 21:32:59 GMT

LATIN1 is a single byte encoding. So it only has 256 possible characters that can be stored. You need to run SAS using UTF-8 encoding to be sure that you can read any character that might be in the XLSX file.

Re: WARNING: Some character data was lost during transcoding in column:

LeonidBatkhan — Thu, 19 Nov 2020 21:46:34 GMT

If your .xlxs file has UTF-8 encoding, and it has some UTF-8 characters that take more than 1 byte you may lose characters due to transcoding if your SAS has LATIN1 encoding. As I indicated in my previous reply, you need to bring your SAS encoding to UTF-8, not your UTF-8 encoded .xlxs file to LATIN1. LATIN1 encoding uses 1 byte per character, while UTF-8 uses up to 4 bytes per character. Therefore you need to match your SAS session encoding to your file encoding, not vice versa.

Re: WARNING: Some character data was lost during transcoding in column:

LL5 — Thu, 19 Nov 2020 21:57:31 GMT

Understood and makes sense. Unfortunately I am not able to change the encoding to 'UTF-8' in my SAS session.

Re: WARNING: Some character data was lost during transcoding in column:

Ksharp — Fri, 20 Nov 2020 12:46:23 GMT

1) Transform your excel file into CSV file by Save As menu.
And import this csv again.

filename a '/Inventory/group/sale_20200930.csv’ encoding='utf8' ;
PROC IMPORT

2) Try utf8 encoding:
filename a '/Inventory/group/sale_20200930.xlsx’ encoding='utf8' ;
PROC IMPORT

or
libname a '/Inventory/group/sale_20200930.xlsx’ inencoding='utf8' outencoding='utf8' ;

Re: WARNING: Some character data was lost during transcoding in column:

LL5 — Fri, 20 Nov 2020 15:22:47 GMT

Thanks Ksharp. The issue is the excel file I have has utf-8 but my SAS session (E.G) has Latin1. As pointed out by Leonid's post earlier, I would need to change my SAS session to utf-8, so adding any encoding option in the code would not solve the problem. Unfortunately, either changing the file from xlsx to csv or change my SAS configuration file from Latin1 to utf-8 would not be doable in my work situation, I could only leave the warning message as it.

Separately, since I have an external file instead of a sas dataset, I can't use inencoding/outencoding because it is not implemented in the XLSX engine, but good to know this technique. Thanks again.

Re: WARNING: Some character data was lost during transcoding in column:

Tom — Fri, 20 Nov 2020 16:10:32 GMT

Talk to whoever in your company is responsible for setting up SAS. It is their job to enable you to be able to run SAS with the settings you need.

If you are running SAS from the command line then they can create a different command (or add an option to the existing command) to launch SAS with proper encoding.
If you are using SAS/Studio or Enterprise Guide or other method to connect to a SAS session running on an application server then they can create a separate connection for you to use to launch a SAS session using the encoding you need.
If you are using Windows and launching SAS from the desktop then they can make "icons" that appear in your Start menu to launch SAS using the encoding you need.

Re: WARNING: Some character data was lost during transcoding in column:

LL5 — Fri, 20 Nov 2020 16:28:40 GMT

Thanks Tom. The SAS programs are ran in batch under Unix, so it may not help even I update my SAS E.G session. I informed the SAS administrator about the warning message, it just seems there might be no way to fix this issue by simply updating the SAS code. But all the information I collected here are helpful for me to understand the reason.

Re: WARNING: Some character data was lost during transcoding in column:

Quentin — Fri, 20 Nov 2020 16:30:49 GMT

Below is untested, but it is possible to provide an excel range when you specify the dataset on the SET statement.

data inv_sept ; 
  set sale."sale_sept$A:C"n; 
run;

That might keep the XLSX engine from reading the extra columns.

Unfortunately, it looks like it only supports contiguous ranges. Below when I give it a noncontiguous range, $A:C,E:F , I still get column D.

data inv_sept ; 
set sale."sale_sept$A:C,E:F"n; 
run;

Re: WARNING: Some character data was lost during transcoding in column:

Tom — Fri, 20 Nov 2020 16:47:14 GMT

@LL5 wrote:

Thanks Tom. The SAS programs are ran in batch under Unix, so it may not help even I update my SAS E.G session. I informed the SAS administrator about the warning message, it just seems there might be no way to fix this issue by simply updating the SAS code. But all the information I collected here are helpful for me to understand the reason.

So currently you are running a command like this:

sas myprogram

So either have them make a new command you can use or add an option to the existing command so that you can instead using a command like one of these.

sas_u8 myprogram
sas -u8 myprogram

Re: WARNING: Some character data was lost during transcoding in column:

LL5 — Fri, 20 Nov 2020 16:58:48 GMT

Hi Quentin, thanks for this alternative and creative solution. I have noncontiguous ranges but this approach would be helpful to other work situation. Thanks again.

Re: WARNING: Some character data was lost during transcoding in column:

LL5 — Fri, 20 Nov 2020 16:59:40 GMT

Thanks Tom again for all the advice.