Dear community!
When using the EG task to import xlsx a data step is generated that refers to an intermediary file with delimited characters. Does anyone know how this is done technically?
The generated code does unfortunately not show how this file is produced starting from the xlsx file.
Yes of course one could use the simpler methods that are normally recommended, i.e. libname with the xlsx engine and proc import. I am particularly interested in the approach EG takes as I am trying to track down some transcoding issues when importing the xlsx.
So ... does anyone happen to know?
Cheers
fja
Hello!
Thank you for very much for your reply Cynthia. And appologies for the late reply .. I have fallen ill in between, this is why. This thread has gone cold meanwhile but let me answer to your post to show appreciation for your contribution.
I have traced back the "culprit" enty, it is a "č" character (a cech c with an inverted accent circonflex). Encoding option is set to latin1 on the system (so I cannot change that). The xls is formated/saved with encoding "windows latin1", changing that to utf-8 or anything different does not change the behaviour.
Using libname xslx does result in the same transcoding errors as before.
We have not managed to come up with a resoluton for this behaviour/warnings so we decided to alter the data source. Not nice but a quick way out. Nevertheless I was courious if someone had some idea I could have play with. Please let me know. 🙂
As Cynthia has suggested one actually needed to go through this step by step with the tech support. But as we have that workaround in place I won't waste their time. 🙂
Cheers to all contributers ... and a Happy New Year to you all! 🙂
--fja
Talk to your SAS administrator. It is entirely possible to create a new SAS server instance that has the UTF-8 encoding setting for any SAS sessions using this server. It is quick and easy to set one of these up, but administrator permissions are required. Then, you just point your EG connection profile to the new server instance.
What OS does your SAS server run on? If it is Windows you could use the EXCEL LIBNAME engine to see if that copes better with encoding issue. If your SAS server runs on Unix, then you can always install the SAS PC Files Server and use the PCFILES LIBNAME engine as another alternative.
What I have heard is that EG now has an option to NOT do that transformation.
Instead there is a way to tell to just upload the file and generate PROC IMPORT code to read it.
Hello Tom!
Thank you for your reply. Yes, there is a tick option at the last step of the import wizard of EG 8.2. Alas its behaviour/the generated code is pretty much unimpressed by having it set. But thank you for pointing this out.
--Fja
If you don't mind watching a little video, this segment covers how EG Import Data works and how you can control it.
Hello Chris!
Thank you for that pointer ... and the nice and well crafted presentation, of course. Alas, the procedure sketched out there seems only be valid vor csv files. Working with EG 8.2 and and xlsx file to import I do not get the options offered that you have mentioned in your video.
Could it be, that this is just implemented for csv and not for xlsx data sources?
--fja
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.