03-26-2014 12:11 PM
I am trying to export sas dataset to csv format and it converts character variables to exponential format in the csv file.
The dataset has two columns id and id1 which has values similar to 101145678, 9845347856 which are character variables.
After exporting it to excel the values are being diosplayed this way 1011E123, 98453E789.
Any help is appreciated.
03-26-2014 12:22 PM
This sounds more like a "feature" of Excel. Open your CSV in a text editor (notepad) and have a look. If the numbers do not have " or ' around them then when Excel reads them in it will think they a numeric and format them as such. I seem to remember a SAS export option to encase quotes around text, will have a look. Another alternative is just to write the export CSV yourself using put statements.
03-26-2014 12:31 PM
Thanks for the replu. I opened it in notepad and it doesnt have quotes around it. I will try to look at the proc export option.
Can you explain the alternate option that u specified. Did you mean using the file option and data _null_ step to dump the csv file using dlm option? I tried it too.. still the same problem.
03-26-2014 12:31 PM
Just to add, in case you were wondering on the write the data yourself - first the export, then the write it by hand:
attrib id id1 format=$20.;
proc export data=have outfile='S:\Temp\Rob\tmp.csv' dbms=csv replace;
03-26-2014 12:40 PM
the proc export result in the same answer.
The data step creates the csv file. Now when i open it it still shows the Exponential numbers. But now when i open it with notepad it has quotes around it
03-26-2014 01:23 PM
Excel is doing the formating/calculations (yes sadly it calculates and potentially loses precision).
Upon opening the CSV in excel,
1. go to the main menu
2. excel options
4. under display options for this worksheet click show formulas in cells instead of their calculated values
There might be some details online to have this set as the default when you open excel but I don't think you could help it on other people's desktop using CSV. CSV contains no metadata and its up to whatever software you use to open it to chose how it does so. Sadly, MS tries so much to accomodate people in having to do less manipulation that they best guess format and calculation wrong, even with DSD (quotes around strings).
03-26-2014 01:26 PM
Alternatively, you could also use RW9's approach with throwing double quotes around but then further more adding an = sign in front of each text string such that instead of having
Reading the equal sign in a cell will tell excel it is a formula. In the case of a string formula aka "12345678901234567890", it will then only display it as a string.
03-26-2014 01:50 PM
Good idea by Vince28 there, hadn't thought about the equals sign. Here is a link to an article on the number format option:
One other alternative is to sidestep the issue, do:
then export that. Excel should then recognise it as text as it looks like ID12345667.
03-26-2014 08:34 PM
A variant on RW9's second alternative is to use a prefixed single quote, which at least up to Excel 2007 is recognised as a character flag (for compatibility with good old Lotus123). The quote may appear initially when opened in Excel but it should subsequently be masked.
Length ID $<one more than in original data> ;
then export that.
03-26-2014 12:39 PM
Ok, yes, that doesn't work either. Can I suggest checking out tagsets.excelxp with excel number formats. Have to leave now, will post some code tomorrow if needed.