Desktop productivity for business analysts and programmers

Leading zeros are removed when generating Excel output

Reply
Contributor
Posts: 28

Leading zeros are removed when generating Excel output

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?

Attachment
Respected Advisor
Posts: 3,063

Re: Leading zeros are removed when generating Excel output

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. 

Contributor
Posts: 28

Re: Leading zeros are removed when generating Excel output


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.

Respected Advisor
Posts: 3,063

Re: Leading zeros are removed when generating Excel output

@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 Smiley Very Happy

 

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. 

Grand Advisor
Posts: 9,582

Re: Leading zeros are removed when generating Excel output

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;

Respected Advisor
Posts: 3,837

Re: Leading zeros are removed when generating Excel output

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?

Capture.PNG

Capture.PNG

Contributor
Posts: 28

Re: Leading zeros are removed when generating Excel output

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

Respected Advisor
Posts: 3,837

Re: Leading zeros are removed when generating Excel output

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/ 

 

 

 

Ask a Question
Discussion stats
  • 7 replies
  • 301 views
  • 2 likes
  • 4 in conversation