topic Re: ods msoffice2k-Leading zeros in output in SAS Programming

ods msoffice2k-Leading zeros in output

Ronein — Thu, 08 Nov 2018 07:52:28 GMT

Hello

In following code the data in excel doesn't includes leading zeros.

What is the way to solve it?

Why option leading_zero="yes" is not working?

data one;
retain zip account;
set sashelp.class;
zip="06555";
account="0345";
run;
ods msoffice2k path="path" (url=none)
         gpath="pathr" (url=none)
         file="file4b.xls"
		 options( leading_zero="yes")
         style=normal;
  proc print data=one noobs;
  Title;
  var zip account;
  var age height / style(data)={htmlstyle="mso-number-format:'##.00'"};
  var weight;
  run;
ods msoffice2k close;

Re: ods msoffice2k-Leading zeros in output

Kurt_Bremser — Thu, 08 Nov 2018 08:31:35 GMT

First of all: don't lie with your filename extension. ODS MSOFFICE2K creates HTML, so the file should have an extension of .html or .htm.

When you look at the file, you'll see that you get a HTML TABLE object. HTML TABLE cells do not have a type, so Excel has to guess what it is. 06555 looks like a number, Excel stores it as such, and uses the default number format which suppresses leading zeroes. The only things that are recognized are the alignment (because that's part of the HTML TABLE cell formatting), and the mso-number-format style property.

Re: ods msoffice2k-Leading zeros in output

RW9 — Thu, 08 Nov 2018 08:58:27 GMT

Stop mixing file types and old syntax. If you want to create an actual Excel file for the modern day, then it will be an XLSX formatted file created using ods Excel. What you are creating there is HTML. Then you are calling it XLS, which it is not, and you wouldn't want an old file format like XLS which is proprietary binary file replaced now by the Open Office file format such as XLSX.

Ods excel:

https://support.sas.com/resources/papers/proceedings17/0169-2017.pdf

If you don't have the latest SAS version (and the question then is why not?), then use ods tagsets.excelxp. This creates XML output which Excel can parse:

https://support.sas.com/resources/papers/proceedings17/0973-2017.pdf

That doesn't create native files though, just xml which is not ideal, but does have a lot of formatting options.

Re: ods msoffice2k-Leading zeros in output

Peter_C — Thu, 08 Nov 2018 09:06:16 GMT

I think the loss of leading zero is an excel formatting issue
Instead of
"##.00'"
Try
"00.00"
Practise in excel choosing different "custom" number formats