Leading zeros are removed when generating Excel output

[error_prone]

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?

[SASKiwi]

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. 

[error_prone]

---

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

[SASKiwi]

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

[Ksharp]

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;

[Patrick]

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?

[error_prone]

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

[Patrick]

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/