Help using Base SAS procedures

Excel converts string to numeric

Reply
Contributor
Posts: 28

Excel converts string to numeric

Hi,

Does any one know of a solution to stop Excel converting string values such as "001" to a numeric field of 1? I've tried several combinations to try and fix the issue including adding a dummy row with values of "000 (dummy)" and this works for the active row but not for subsequent rows. Also tried adding a single quote of the start of the string (" '001") and this works but the output is '001 which isnt ideal.

Any help would be appreciated. I'm running on SAS 9.1.3.

Frequent Contributor
Frequent Contributor
Posts: 94

Re: Excel converts string to numeric

Excel determines the format of the column based on the first few rows (first 5 I think). If the value in these rows is in a different format to those in the rest, incorrect format might result.

Adding a single quote, which you have done, usually works. If not, I tend to apply a desired format to that column after importing in SAS and it works fine.

Super Contributor
Posts: 578

Re: Excel converts string to numeric

are you importing or exporting?

Frequent Contributor
Frequent Contributor
Posts: 94

Re: Excel converts string to numeric

Importing.

Super Contributor
Posts: 578

Re: Excel converts string to numeric

using enterprise guide wizard or coding directly?

Frequent Contributor
Frequent Contributor
Posts: 94

Re: Excel converts string to numeric

Coding - I find it much easier than using the wizard.

Super Contributor
Posts: 578

Re: Excel converts string to numeric

sorry...I got confused about the direction.  You're importing into Excel...

I've seen it done via HTML by formatting the cell as TEXT:

ods html file='temp.xls';

proc print data=one;

var acct_no / style={htmlstyle="vnd.msexcel.numberformat:@"};

var zipcode;

run;

ods html close;

Another option I've seen is to use ="001" rather than the '.

Contributor
Posts: 28

Re: Excel converts string to numeric

I'm using coding directly and exporting to excel using ODS.

Linlin - so you would suggest creating several dummy rows to force Excel to handle the column as a text field?

Super Contributor
Posts: 1,636

Re: Excel converts string to numeric

Yes. That is what I do when I work with excel files. 

SAS Super FREQ
Posts: 8,864

Re: Excel converts string to numeric

Hi:

Regarding using ODS to go FROM SAS to Excel -- depending on your ODS destination, you can use either HTMLSTYLE or TAGATTR to pass Microsoft-specific formats from SAS to Excel. See this user group paper for detailed information about using either HTML-based destinations or XML-based destinations to have your numbers (such as leading zeroes, decimal points, etc) treated correctly when Excel opens the ODS result file.

http://support.sas.com/resources/papers/proceedings11/266-2011.pdf

(Technically, when you use ODS to send results to Excel, you are just creating an ASCII text file that Excel knows how to open and render. That ODS result file is NOT a true, biinary Excel file. Only PROC EXPORT or the LIBNAME engine can create true, binary Excel format data files. The advantage of using ODS is that with HTML-based or XML-based result output, you can add colors and fonts and style formatting to your worksheets AND, you can send Microsoft formats from SAS to Excel -- which you cannot do with the LIBNAME engine or PROC EXPORT).

About an import from Excel to SAS, you either use PROC IMPORT, or the LIBNAME engine, or use your own code to read the data from Excel into SAS and transform you variables from numeric to character or vice versa. Using code gives you the kind of control you want over informats and variable types.

cynthia

Contributor
Posts: 28

Re: Excel converts string to numeric

Posted in reply to Cynthia_sas

Recommended reading from Cynthia Smiley Happy

Super Contributor
Posts: 1,636

Re: Excel converts string to numeric

I usually add 2 or 3 dummy rows (characters). It wound not work when you add 000 as dummy row.

Frequent Contributor
Posts: 97

Re: Excel converts string to numeric

hi

i find some short cut in excel only...

But it will give FOUR zeros for  before the number...

Please find the screen shot...

Regards

ALLU

Attachment
Ask a Question
Discussion stats
  • 12 replies
  • 1534 views
  • 1 like
  • 6 in conversation