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,
220831262559% |
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?
Cheers
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.
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.
Cheers
Paul.
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?
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.
Have a look at https://blogs.sas.com/content/sasdummy/2012/12/06/copy-files-in-sas-eg/.
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.
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"):
EG 4.3:
"x1" "220831262559%"
EG 7.15:
x1 220831262559%
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)
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.
Thanks again.
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)
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.
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.
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.