07-03-2018 10:51 AM
I've got a dataset that I'm exporting from EG 5.1 using the Export Wizard, it has one large percentage in one of the columns,
The number appears correctly in the dataset that is exported, but in the resulting .csv file it is written as ***% This opens in Excel as text and causes a Excel to throw a wobbly when averaging the results in a Pivot Table.
Along similar lines I have a number of percentages that are coming out as 4.00E+07 or similar, these open in Excel as 40000000, which is fine, but it loses all of the resolution in the number. So there are 10 values of 1.00E+01, and I could do with knowing what the actual numbers are so they can be compared properly.
What's going on and how do I fix it?
07-03-2018 11:01 AM
Just use the percent format with a sufficient length:
filename out temp; data test; x1 = 220831262559/100; format x1 percent20.; put x1=; run; data _null_; file out; set test; put x1; run; data want; infile out truncover; input x1 $20.; run;
As you will see, no format overflow happens at all stages.
07-03-2018 12:29 PM
Hello, thanks for your help!
The percentage is given a length of 15 when it is created, which should be big enough shouldn't it? The format obviously gets carried forward throughout the process flow as it goes through numerous steps to get to the point it's exported.
The length doesn't explain why every percentage over 1000 is being exported in scientific notation as that's only five characters long.
I can't follow your solution as I can't write files to the server, I'm restricted to only using Export Wizard. Don't ask why, it's not something I can change and a huge cause of frustration.
07-03-2018 01:11 PM
This looks like a problem in EG. I will test it tomorrow with 4.3, 6.1 and 7.15 to check if and where it's been fixed. SAS itself (data step) gets it right, obviously.
And tell the **** who keeps you from making proper use of the server to google "Mordac the Preventer".
Don't you see "Files" when you open SASApp in the server list?
07-04-2018 03:19 AM
07-04-2018 03:39 AM
The default setting for the File Navigation in EG is the user's home directory, which makes a lot of sense; I can imagine no valid reason to change that, given a solid setup of the server as such.
This plugin allows to copy any type of file to/from any location on server and desktop (given the necessary OS permissions on source and target). It can easily be installed in your own user tree on the Windows desktop (no admin privileges needed).
In EG 7, this plugin has become a standard task.
07-05-2018 07:30 AM
07-04-2018 02:04 AM
So I tested this today. SAS is 9.4M5 on AIX.
Here are the files (created from my example dataset with "Export as a Step in the Project"):
Both files, when opened in Excel 2013, showed a correct value after expanding the column to the necessary width. Same in LibreOffice after automatic run of the import wizard.
Couldn't test it with 6.1, though, as that seems to have fallen victim to dll hell (can't find the connection to the metadata server from the same desktop where the other versions work, oh the joys of Windows)
07-04-2018 03:44 AM
Thanks, I've tried changing the format to PERCENTN20. and rerun the flow. I have the dataset that is being exported still containing the correct number, the text file that the Export Wizard creates as the very next step still contains one ***%.
Are there any setting hidden away that could cause this? It seems odd that it's doing this and also rounding percentages over 1000 into Standard Notation.
07-04-2018 04:21 AM
Usually, at this point I'd ask for a sample of your dataset with the value that fails, using the macro from my second footnote. But since you have troubles retrieving the SAS code file it creates, it won't do us any good.
(the macro writes a file with a data step that will create an exact logical copy of your dataset, values, lengths, labels, formats etc all the same)
07-05-2018 08:01 AM
Yeah, sorry, I'm a bit stuck for providing copies of files. All I can really say is that I've got a table that contains the percentage correctly, the export wizard creates the text file in EG (and the exported .csv file) with ***% in for the larger results and scientific notation for anything over 999% and less than 9E7%.
All I can think is to try exporting different file types, but I'm n sure that will work.
07-05-2018 08:13 AM
I can only see two further avenues for research:
- it could be a bug introduced with EG 5, and fixed later (that's why both my really old and really new versions work)
- it could depend on the backend version of SAS
Since it's very hard to diagnose from here, given your restrictions, I recommend to get in touch with SAS technical support.
And yes, raise all kinds of hell to get those restrictions lifted. My users (I'm the data warehouse admin here) all have access to SSH connections (PuTTy, WinSCP etc), and can access their home directories from EG. Never had a problem with it. The times where I needed to restore a user's files all came from hickups with pure SAS code.