topic Re: Leading zeros are removed when generating Excel output in SAS Enterprise Guide

Leading zeros are removed when generating Excel output

error_prone — Thu, 10 Nov 2016 16:48:12 GMT

Using EG 7.12 with latest hotfix applied. When setting result type to Excel leading zeros are removed.

Code:

data work.example;
   length Zero $ 2 Text $ 10;
   input Zero Text;
   datalines;
01 first
02 second
03 third
13 narf
42 perfect
run;

proc print data=work.example noobs;
run;

How to fix this without coding Excel-output?

Re: Leading zeros are removed when generating Excel output

SASKiwi — Thu, 10 Nov 2016 18:38:05 GMT

This isn't a bug, this is normal Excel behaviour - just try typing 01 into an Excel cell yourself, press enter and watch the leading zero disappear.

One way to fix this is to add a single quote in front of the zero.

Re: Leading zeros are removed when generating Excel output

Ksharp — Fri, 11 Nov 2016 10:55:23 GMT

Yeah, That was supposed to be for Excel. Add comma or a TAB character before it.

data work.example;
   length Zero $ 4 Text $ 10;
   input Zero Text;
zero=cats('09'x,zero);
   datalines;
01 first
02 second
03 third
13 narf
42 perfect
run;

Re: Leading zeros are removed when generating Excel output

error_prone — Fri, 11 Nov 2016 15:12:48 GMT

@SASKiwi wrote:
This isn't a bug, this is normal Excel behaviour - just try typing 01 into an Excel cell yourself, press enter and watch the leading zero disappear.

One way to fix this is to add a single quote in front of the zero.

Setting cell-type to text stops Excel from removing leading zeros. Changing values is not acceptable.

Re: Leading zeros are removed when generating Excel output

Patrick — Sun, 13 Nov 2016 00:24:02 GMT

Using EG version 7.12 HF5 (7.100.2.3472) (64-bit) with SAS under Windows and your sample data exactly as posted, things work for me.

How are you creating the Excel file?

Re: Leading zeros are removed when generating Excel output

error_prone — Mon, 14 Nov 2016 14:19:42 GMT

I used the new result type "Excel" found in Program | Properties | Results.

Re: Leading zeros are removed when generating Excel output

Patrick — Mon, 14 Nov 2016 14:45:23 GMT

Yes, this way I'm also loosing the leading zeros even though it's in SAS a character variable. As the options says "Excel" I'd say it's a reasonable expectation that the leading zero's are kept - so agree: It's an undocumented feature.

If you just need a solution then use one of the other ways of creating an Excel. It wouldn't hurt though if you raise a SAS TechSupport track so that this gets fixed. https://support.sas.com/techsup/contact/

Re: Leading zeros are removed when generating Excel output

SASKiwi — Tue, 15 Nov 2016 21:08:28 GMT

@error_prone: "Changing values is not acceptable." Well welcome to the wonderful world of Excel then as it makes all sorts of assumptions about what you type into a "General" column

My guess is you are getting the default action the way you are sending results to Excel. As others have stated there are other ways to export to Excel that send through more formatting info.