Desktop productivity for business analysts and programmers

why SAS PROC IMPORT twisted the values in EXCEL 2013 file?

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 12
Accepted Solution

why SAS PROC IMPORT twisted the values in EXCEL 2013 file?

why SAS PROC IMPORT twisted the values in EXCEL 2013 file?

 

We have EXCEL 2013 file with values such as C&IB, but after reading my PROC IMPORT, the results are C&IB.

 

Changed From     C&IB    to     C&IB   

 

Here is the code;

proc import datafile="&Maps./LOBOR_Map_samples.xlsx"

out=map

dbms=xlsx replace;

getnames=YES;

run;

 

Who knows what is wrong?

 

Thanks a lot!


Accepted Solutions
Solution
2 weeks ago
SAS Employee
Posts: 129

Re: why SAS PROC IMPORT twisted the values in EXCEL 2013 file?

Posted in reply to fankaiqing

Hi @fankaiqing

 

This is a known issue with the XLSX engine, reported to R&D.

If you can't use @Reeza 's solution for now, a couple of other workarounds are:

  - Use the EXCELCS engine. You may have to install SAS PC Files Server.

  - Use the XLS engine (after you save your file to LOBOR_Map_samples.xls).

 

Hope that helps.

 

Cheers,
Damo

View solution in original post


All Replies
Super User
Posts: 20,226

Re: why SAS PROC IMPORT twisted the values in EXCEL 2013 file?

Posted in reply to fankaiqing
I don’t know why it happens, but you can use HTMLDECODE to correct the variables in question.

Solution
2 weeks ago
SAS Employee
Posts: 129

Re: why SAS PROC IMPORT twisted the values in EXCEL 2013 file?

Posted in reply to fankaiqing

Hi @fankaiqing

 

This is a known issue with the XLSX engine, reported to R&D.

If you can't use @Reeza 's solution for now, a couple of other workarounds are:

  - Use the EXCELCS engine. You may have to install SAS PC Files Server.

  - Use the XLS engine (after you save your file to LOBOR_Map_samples.xls).

 

Hope that helps.

 

Cheers,
Damo

Occasional Contributor
Posts: 12

Re: why SAS PROC IMPORT twisted the values in EXCEL 2013 file?

Yes.

When I open the xlsx file, the value in the second column is with a formula relationship with the first column, For example, when you click the 3rd row cell of the second column, that is, B3 , the cell B3 will not show its value but "=A3". It means that this cell has relationship with the first column.

The solution is that we copy all its values in this xlsx file into another one to discard the formula, keep original values as text.

Thanks so much!
Super User
Posts: 8,062

Re: why SAS PROC IMPORT twisted the values in EXCEL 2013 file?

Posted in reply to fankaiqing

Open the .xlsx file with a ZIP program, and navigate to the \xl\worksheets folder within the file. Open sheet1.xml with a text editor.

When you scroll far enough to the right of the second line, you'll find that your values are stored like that (HTML encoding) by Excel; it seems that proc import gives you that string.

 

If you did the sensible thing and stored your table to a .csv file, and imported that, you would not have the problem.

For the umpteenth time: DO NOT USE EXCEL FILES FOR DATA TRANSFER!

 

BTW: your Excel file is 18K in size, the .csv created from that is just 1K. I rest my case.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 131 views
  • 1 like
  • 4 in conversation