SAS converting character variables to numeric while exporting to CSV

Reply
Occasional Contributor
Posts: 8

SAS converting character variables to numeric while exporting to CSV

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.

Super User
Super User
Posts: 7,407

Re: SAS converting character variables to numeric while exporting to CSV

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.

Occasional Contributor
Posts: 8

Re: SAS converting character variables to numeric while exporting to CSV

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.

Super User
Super User
Posts: 7,407

Re: SAS converting character variables to numeric while exporting to CSV

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;

Occasional Contributor
Posts: 8

Re: SAS converting character variables to numeric while exporting to CSV

Both the option still result in the same problem

Occasional Contributor
Posts: 8

Re: SAS converting character variables to numeric while exporting to CSV

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

Super Contributor
Posts: 339

Re: SAS converting character variables to numeric while exporting to CSV

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

Super Contributor
Posts: 339

Re: SAS converting character variables to numeric while exporting to CSV

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

Super User
Super User
Posts: 7,407

Re: SAS converting character variables to numeric while exporting to CSV

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.

Super Contributor
Posts: 644

Re: SAS converting character variables to numeric while exporting to CSV

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

Super Contributor
Posts: 644

Re: SAS converting character variables to numeric while exporting to CSV

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

Richard

Super User
Super User
Posts: 7,407

Re: SAS converting character variables to numeric while exporting to CSV

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.

Ask a Question
Discussion stats
  • 11 replies
  • 2059 views
  • 6 likes
  • 4 in conversation