BookmarkSubscribeRSS Feed
error_prone
Barite | Level 11

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?


Bug_Excel_Result.PNG
7 REPLIES 7
SASKiwi
PROC Star

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
Barite | Level 11

@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
PROC Star

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

Ksharp
Super User
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
Opal | Level 21

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

error_prone
Barite | Level 11

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

Patrick
Opal | Level 21

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/ 

 

 

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 1938 views
  • 2 likes
  • 4 in conversation