How to peserve the leading zeros when exporting SAS data to Excel/CSV ?

[Justin]

Suppose you have some character variables in a SAS data set, when you write it out to Excel or CSV, they will be converted into numeric by default and the leading zeros will be lost. How can we preserve these leading zeros?

Method A: Headtext Option

 data E;
 input ID $ Name $;
 cards;
 0001  Tom
 0003  Jack
 6005  Gary
 ;
 run;

filename AAA 'file path/AAA.xls';
ODS listing;
 ODS HTML file=Amanda 
 headtext="<style> td {mso-number-format:\@}</style>";

 proc report data=E;
 column ID name;
 define id/display 'ID' width = 10 format = $10. ;
 define name/display;
 run;

ODS HTML close;
ODS listing;

[Justin]

Method 2: Use ODS tagsets.ExcelXP destination

Microsoft Excel uses the general format when importing data values that do not have an Excel format applied. This general format attempts to determine how the value should be formatted. For example, an account number that is not alpha numeric and has leading zeroes will lose the leading zero. The
same problem occurs when you type a value in a cell of Excel.

To get around this problem, the Excel Text format can be applied so that the value comes over the same way it was displayed in SAS. How this is done depends on the ODS destination that you use to generate the file. If the ExcelXP destination is used, the Excel text format can be applied using the
format: parameter within the TAGATTR= attribute. Using the MSOffice2k/HTML/HTML3 destinations, the Microsoft Office CSS style property mso-number-format can be used with the same text format.

Example:

 data one;
   input account name $;
 cards;
 023456 Bob
 054556 Henry
 034456 Wes
 ;
 run;

 /* ExcelXP destination */
 ods tagsets.ExcelXP file="temp.xls";
 proc print data=one;
 var name;
 var account / style(data)={tagattr="format:@"};
 format account z6.;
 run;
 Ods tagsets.ExcelXP  close;

 /* MSOffice2k destination */
 ods MSoffice2k  file="temp1.xls"; 
 proc print data=one;
 var name;
 var account / style(data)={htmlstyle="mso-number-format:\@"};
 format account z6.; 
 run;
 Ods msoffice2k close;

Note: The above options work with tagsets.ExcelXP, MSOffice2k, but does not work with ODS HTML. Please refer below for more information: http://support.sas.com/kb/32/414.html