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?
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.
@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.
@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.
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;
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?
I used the new result type "Excel" found in Program | Properties | Results.
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/
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.