excelprintingsaswhitespace

How to retain empty spaces in a proc print to excel? (SAS)


I have a stats table I want to export to Excel. Let's say I formatted some of it using the code below.

data transposed_results;
set transposed_results;
Variable = '   ' || put(Variable, 32.);  /* Adds 3 spaces before the text */
run;

When I open the table I see the 3 spaces just as I want. Great. But when I try to export it to Excel I can't retain the spaces. Is there any way I can keep them? Or does anyone know an alternative to how I could keep empty spaces in front of some of my variables? I'm trying to create a macro to export my results to an Excel sheet to avoid manual formatting every time.

/*Code to export to excel*/
ods excel file="C:\filepath"  /*Update to your file path*/
    options(sheet_name="Descriptive_Table" embedded_titles="no");

ods noproctitle;  /* Removes "The SAS System" title */
title;  /* Clears any active titles */
footnote;  /* Clears footnotes */

proc print data=Descriptive_Table noobs;
    format Variable $char32.; /* Format to show the spaces in the output but doesn't work */
run;

ods excel close;

Solution

  • That is an ODS issue. The same thing will happen with PDF and HTML destinations.

    You could just EXPORT the dataset to EXCEL instead of using ODS.

    proc export data=Descriptive_Table dbms=xlsx 
      file="C:\filepath\filename.xlsx"  replace
    ;
      sheet = "Descriptive_Table";
    run;
    

    Use the XLSX engine.

    libname out xlsx "C:\filepath\filename.xlsx";
    data out.Descriptive_Table;
      set Descriptive_Table;
    run;
    

    Or tell ODS to print the column "as is" by using the ASIS=ON style option.

    ods excel file='c:\downloads\asis.xlsx';
    proc print style(column)=[asis=ON];
    run;
    ods excel close;
    

    enter image description here