Format Issue when generating dataset into Excel

BNG — Thu, 05 May 2011 12:09:40 GMT

Hi,
I have a question about generating a customer ID to Excel. Please take a look of the following example.

Data Customer;
Input Cust_ID $5. Deposite;
Cards;
00155 200
00166 400
;
Ods listing close;
Ods html body = “D:\MyFolder\CustList.xls”;
Proc print data = Customer noobs; Run;
Ods html close;
Ods listing;

As shown in Excel report, the Cust_ID becomes only 3 digits number (e.g. 155, 166…).
How can we avoid this format issue when exporting the dataset into Excel?

Thanks for your answer.
BNG

Re: Format Issue when generating dataset into Excel

Cynthia_sas — Thu, 05 May 2011 17:17:55 GMT

Hi:
Microsoft Excel treats numbers with a General format, once it gets them. Since you are using ODS HTML, my recommendation would be to:

1) switch to ODS MSOFFICE2K -- which is Microsoft "flavor" of HTML and
2) use the HTMLSTYLE attribute with an mso-number-format to tell Microsoft to use a leading zero number format.

There are examples of this method in my recent SAS Global Forum paper:
http://support.sas.com/resources/papers/proceedings11/266-2011.pdf

The leading zero example is on pages 6 and 7. You will want the HTMLSTYLE example (not the TAGATTR example -- TAGATTR is for use with TAGSETS.EXCELXP).

cynthia

topic Format Issue when generating dataset into Excel in Microsoft Integration with SAS

Format Issue when generating dataset into Excel

Re: Format Issue when generating dataset into Excel