SAS Office Analytics, SAS Add-In for Microsoft Office, and other integrations

Format Issue when generating dataset into Excel

Reply
Occasional Contributor BNG
Occasional Contributor
Posts: 10

Format Issue when generating dataset into Excel

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
SAS Super FREQ
Posts: 8,643

Re: Format Issue when generating dataset into Excel

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
Post a Question
Discussion Stats
  • 1 reply
  • 184 views
  • 0 likes
  • 2 in conversation