Home Forums Main Forums SAS Forum How to peserve the leading zeros when exporting SAS data to Excel/CSV ?

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

     Justin updated 7 months, 1 week ago 1 Member · 2 Posts
  • Justin

    Administrator
    February 7, 2021 at 5:25 pm

    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

  • Justin

    Administrator
    February 7, 2021 at 5:20 pm

    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;

Log in to reply.

Original Post
0 of 0 posts June 2018
Now