BookmarkSubscribeRSS Feed
paulrockliffe
Obsidian | Level 7

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

11 REPLIES 11
Kurt_Bremser
Super User

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.

 

paulrockliffe
Obsidian | Level 7

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.

Kurt_Bremser
Super User

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?

paulrockliffe
Obsidian | Level 7
Ha ha, I'll Google that now and pass the message up the chain. There is a files area, but I can't write to it and there's no way to move files in and out of it even if I could. I've tried all sorts, I think there's a thread on here somewhere about it, from memory I managed to write to $HOME without it failing, but I can't see that folder to know if the file went and obviously can't get it out. There are reasons it was setup that way, though people are slowly coming round to the view that those reasons don't outweigh how restrictive it is. I spend a lot of my time building clunky workarounds so I can automate re-runs of projects as there's no way to automate the export bit of the process without server access!
Kurt_Bremser
Super User

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.

 

paulrockliffe
Obsidian | Level 7
Sorry, just picked up this post; I've already looked at that plugin, I can't install it as the windows desktop is also fully locked down. It's part of EG7.13 onwards, we're due an upgrade to....... 7.12 soon. 😞 I'm not sure it helps if I can't easily write to the server, but it may be an option to explore. Anyway, that's a side issue for now....
Kurt_Bremser
Super User

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)

paulrockliffe
Obsidian | Level 7

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.

Kurt_Bremser
Super User

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)

paulrockliffe
Obsidian | Level 7

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.

Kurt_Bremser
Super User

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 11 replies
  • 1100 views
  • 0 likes
  • 2 in conversation