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;
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;