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;

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;

• excel
• printing
• sas
• whitespace

1 Answer 1

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;