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.
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.
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.
Just to add, in case you were wondering on the write the data yourself - first the export, then the write it by hand:
data have;
attrib id id1 format=$20.;
id="101145678";
id1="9845347856 ";
output;
id="1010101100";
id1="8498638940683";
output;
run;
proc export data=have outfile='S:\Temp\Rob\tmp.csv' dbms=csv replace;
run;
data _null_;
set have;
file "s:\Temp\Rob\tmp2.csv";
mystring='"'||strip(id)||'","'||strip(id1)||'"';
put mystring;
run;
Both the option still result in the same problem
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
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
3. advanced
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).
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
name,valuec,valuen
"Vincent","12345678901234567890",12345678901234567890
you have
name,valuec,valuen
="Vincent",="12345678901234567890",=12345678901234567890
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.
Vincent
Good idea by Vince28 there, hadn't thought about the equals sign. Here is a link to an article on the number format option:
http://support.sas.com/resources/papers/proceedings13/316-2013.pdf
One other alternative is to sidestep the issue, do:
data have;
set have;
id="ID"||strip(id);
...
run;
then export that. Excel should then recognise it as text as it looks like ID12345667.
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.
data have;
Length ID $<one more than in original data> ;
set have;
ID="'"||strip(id);
...
run;
then export that.
Richard
Should mention that the single quote does not work for Apple OSX version of MS Excel 2008.
Richard
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.
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.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.