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!
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
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
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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.