topic Re: Excel converts string to numeric in SAS Procedures

Excel converts string to numeric

Mikeyjh — Fri, 01 Feb 2013 09:06:06 GMT

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.

Re: Excel converts string to numeric

VD — Fri, 01 Feb 2013 13:27:04 GMT

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.

Re: Excel converts string to numeric

DBailey — Fri, 01 Feb 2013 13:30:48 GMT

are you importing or exporting?

Re: Excel converts string to numeric

VD — Fri, 01 Feb 2013 13:40:22 GMT

Importing.

Re: Excel converts string to numeric

Linlin — Fri, 01 Feb 2013 13:42:46 GMT

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

Re: Excel converts string to numeric

DBailey — Fri, 01 Feb 2013 13:48:56 GMT

using enterprise guide wizard or coding directly?

Re: Excel converts string to numeric

VD — Fri, 01 Feb 2013 13:51:00 GMT

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

Re: Excel converts string to numeric

Mikeyjh — Fri, 01 Feb 2013 13:57:59 GMT

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?

Re: Excel converts string to numeric

Linlin — Fri, 01 Feb 2013 14:05:10 GMT

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

Re: Excel converts string to numeric

DBailey — Fri, 01 Feb 2013 14:06:43 GMT

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

Re: Excel converts string to numeric

allurai0412 — Fri, 01 Feb 2013 15:29:57 GMT

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

Re: Excel converts string to numeric

Cynthia_sas — Sat, 02 Feb 2013 15:00:01 GMT

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

Re: Excel converts string to numeric

Mikeyjh — Sun, 03 Feb 2013 17:54:14 GMT