ods excel file="F:\want.xlsx" style=journal; ods excel options(sheet_name='Want' autofit_height='yes' width_fudge='0.58'); proc report data=want nowindows headline headskip ; column location ('DATE' date ) ('TOTAL' total) ('ALL' all) ('STUDENTS WITH VAX1' vax1) ('STUDENTS WITH VAX2' vax2) (STUDENTS WITH VAX3' vax3) ('STUDENTS WITH VAX4' vax4); define location / group order; define date / group order=internal descending ' '; define total / analysis 'NUMBER' format=comma8.; define vax1 / display right 'PERCENT' format=percent7.2 style(column)={tagattr="format:##0.00\%"}; define vax2/ display 'PERCENT' format=percent7.2 style(column)={tagattr="format:##0.00\%"}; define vax3 / display 'PERCENT' format=percent7.2 style(column)={tagattr="format:##0.00\%"}; define vax4 / display 'PERCENT' format=percent7.2 style(column)={tagattr="format:##0.00\%"}; define vax5 / display 'PERCENT' format=percent7.2 style(column)={tagattr="format:##0.00\%"}; compute after location; line ' '; endcomp; run; ods excel close; run;
I have three questions:
1) How do you get SAS not to display the % sign in the SAS output? The vaccine variables need to be multiplied by 100 to get a percent but how can I not display the % sign?
2) I was trying to get SAS to multiply the vax variables by 100 but they ended up being displayed in the Excel output as 10000.00% instead of 100% or 9500% instead of 95.00% even though the actual value is 0.100 and 0.95. Am I doing something wrong?
2) Some of the text in the variable 'Location' is made up of two words. When SAS exports into Excel, the location names with two words don't stay on the same line but overflows onto the next line (same cell in Excel, not a new row) and the row height is doubled. How do you keep the text on the same row so all row heights are equal?
Thanks!
If you are using SAS 9.4M4 or later, you can use the FLOW= suboption.
@Chevell_sas wrote a blog post that mentions the text wrapping issue. See Tips for Using the ODS Excel Destination for more details.
Example code:
%let folder=/folders/myfolders/ODS Excel examples;
* some of the player names in SASHELP.BASEBALL are long. in example
* below, some player names will wrap in Excel output. if you disable
* wrap text in Excel, you will notice that some spaces are lost. player
* names are Lastname, Firstname. the names that were wrapped lose the
* space between the comma and the Firstname. ;
ods excel file="&folder/long_text.xlsx"
options(sheet_name='Using defaults'
index='on');
proc report data=sashelp.baseball;
column name team league division natbat nhits nruns nrbi;
run;
* flow= option is new for SAS 9.4M4. for more details, see
* https://blogs.sas.com/content/sgf/2017/02/20/tips-for-using-the-ods-excel-destination/ ;
ods excel options(flow='tables'
sheet_name='Using flow option');
proc report data=sashelp.baseball;
column name team league division natbat nhits nruns nrbi;
run;
* ok, long names no longer wrap, but now the rows where the names had
* wrapped are bigger than the other rows. use row_height= option.
* there are 7 parameters for row_height. 0 means use default from
* style. ;
ods excel options(flow='tables'
row_heights='0,14,0,0,0,0,0'
sheet_name='Using flow and row_height');
proc report data=sashelp.baseball;
column name team league division natbat nhits nruns nrbi;
run;
ods excel close;
The screen shot below shows the player's name column with text wrapping.
Screen shot showing long text wrapping in ODS Excel output
If you disable text wrapping in Excel, you may notice that you lose some blank spaces in the output. Player names are listed as Lastname, Firstname. The text that had wrapped loses the space between the comma and the first name. The screen shot below shows the name that had wrapped after I disabled text wrapping in Excel.
Screen shot showing long text after disabling text wrapping in Excel
The flow suboption prevents the long names from wrapping, but now the row height for long names appears to be twice the row height of the other rows.
Flow suboption prevents text wrapping, but some rows are twice as tall as other rows
Using the row_height suboption with the flow suboption produces nice output.
Screen shot showing long text not wrapping, all rows in body of report have same row height
You are using a version of SAS before the FLOW option was introduced. You have SAS 9.4M2. FLOW is available in SAS 9.4M4 or SAS 9.4M5.
I'm an end-user at my organization, so I don't know much about how the SAS license works when new versions are released.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: