11-28-2017 03:38 PM
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!
11-29-2017 03:32 AM
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
11-28-2017 04:00 PM
11-29-2017 03:32 AM
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
12-04-2017 10:32 AM
11-29-2017 03:43 AM
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.
Need further help from the community? Please ask a new question.