Desktop productivity for business analysts and programmers

proc import "Some character data was lost during transcoding in column"

Reply
Occasional Contributor
Posts: 7

proc import "Some character data was lost during transcoding in column"

Hi,

 

I am trying to import an xlsx document with a cell containing a string that includes "greater than" ("") sign. I am getting the following note "WARNING: Some character data was lost during transcoding in column: MYVARIABLE at obs 18.". Below is the full code. How can I remove this warning? I don't have the option to change the content or type (xlsx) of the spreadsheet. I am working in SAS 9.4 in Enterprise Guide.

 

 %let specs = /path/myspreadsheet.xlsx;

 

PROC IMPORT
DATAFILE="&specs."
OUT=TS
DBMS=xlsx
REPLACE;
GETNAMES=YES;
RUN;

 

Thanks!

 

Claudia

Super User
Posts: 11,105

Re: proc import "Some character data was lost during transcoding in column"

You may be able to use the LIBNAME Excel pointed to your source.

then instead of import you would have

 

data ts;

   set lib.sheet;

run;

where lib is the library name you assign and "sheet" is one of the data sets in the library.

 

But you may still have an issue if the cause is character encoding. Not changing the content or file type (I assume you mean you can't save as CSV and see if that will import) may mean that you'll never get rid of warning.

Super User
Posts: 7,399

Re: proc import "Some character data was lost during transcoding in column"


claudiajc wrote:

Hi,

 I don't have the option to change the content or type (xlsx) of the spreadsheet.


Of course you have the option. Even if you don't have Excel installed, LibreOffice is available on all common platforms and can be used to convert the excel dungheap into a nice .csv.

 

Excel files are NOT, I repeat, NOT suited for proper transfer of tabular data.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 7

Re: proc import "Some character data was lost during transcoding in column"

Thanks for your replies.Yes, the warning is caused by a character encoding issue. The original text in the xlsx file is "score ≥0.5" and the imported text in my work sas file is "score 0.5". P

And I want to clarify I do have Microsoft Excel in my computer. (sorry,  did not explain myself well). What I cannot do is modify the xlsx file. This is because the instruction I was given is to import that particular file. 

PROC Star
Posts: 1,143

Re: proc import "Some character data was lost during transcoding in column"

I just had to go through this wonderful process...what a pain!

 

No guarantees, but hopefully this will get you going.

 

First, you need to figure out how your spreadsheet is encoded. My source data wasn't a spreadsheet, but I think this will work. In Excel, do File | Save As | click More options | click Tools | click Web options | click Encoding. Hopefully, this will show you what your spreadsheed is stored with.

 

Once I knew this, and had my data imported, running this fixed my up (in my case the data was UTF-8).

 

/* Set the encoding of the dataset to utf-8 */

proc datasets lib=work nolist;

modify indata / CORRECTENCODING='utf-8';

quit;

 

 

Good luck!

  Tom

Ask a Question
Discussion stats
  • 4 replies
  • 1129 views
  • 0 likes
  • 4 in conversation