BookmarkSubscribeRSS Feed
spoorthy
Calcite | Level 5

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.

11 REPLIES 11
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

spoorthy
Calcite | Level 5

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

spoorthy
Calcite | Level 5

Both the option still result in the same problem

spoorthy
Calcite | Level 5

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

Vince28_Statcan
Quartz | Level 8

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).

Vince28_Statcan
Quartz | Level 8

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

RichardinOz
Quartz | Level 8

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

RichardinOz
Quartz | Level 8

Should mention that the single quote does not work for Apple OSX version of MS Excel 2008.

Richard

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

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!

How to Concatenate Values

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.

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
  • 5573 views
  • 6 likes
  • 4 in conversation