The SAS Output Delivery System and reporting techniques

ods excel How can I control which columns get printed on a page?

Reply
New Contributor
Posts: 2

ods excel How can I control which columns get printed on a page?

I have a report created using Excel ODS and Proc Print.  When I print the resulting Excel file, the right-most column (D) does not print with the rest of the page (but rather on a separate page).  I've tried using absolute_column_width to reduce the size of column A (which is wider than all other columns) but it has no effect. I also tried fittopage='1' but that seems to apply to rows as well as columns and results in teeny-tiny unreadable print.  How can I control the page width breaks?

Here's my code:

ODS excel FILE="&QTRLYRPT..xlsx";
ods noproctitle;
ods excel options (
embedded_titles='ON'
embed_titles_once='ON'
EMBEDDED_FOOTNOTES='ON'
EMBED_FOOTNOTES_ONCE='ON'
gridlines='ON'
orientation='LANDSCAPE'
sheet_interval='NONE'
sheet_name="Quarterly Report"
row_repeat='1-8'
absolute_column_width_='3,1,1,1,1,1,1'
);

PROC PRINT DATA=II LABEL split='|';
ID _NAME_;
VAR COL1-COL4;
LABEL
_NAME_='II. SELECTED EXIT CATEGORY SUMMARY |(Related Assistance Services-Only Exiters Excluded)'
COL1 = 'All Career Service Exiters'
COL2 = 'Classroom Training'
COL3 = 'On-the-job Training'
COL4 = 'Work Experience'
;
format _name_ $IIROWS. ;
RUN;

PROC Star
Posts: 631

Re: ods excel How can I control which columns get printed on a page?

Posted in reply to MaryHancock

Please create your code with some sashelp datasets, so that we will be able to run and see or provide the sample data you have.

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...

 

Thanks,
Suryakiran
New Contributor
Posts: 2

Re: ods excel How can I control which columns get printed on a page?

Posted in reply to SuryaKiran

Attached contains my SAS code, the data used, and the resulting excel output.  When you view it in Excel Page Layout, you will see that one column spills over onto another page at the right.  

Attachment
Frequent Contributor
Posts: 123

Re: ods excel How can I control which columns get printed on a page?

Posted in reply to MaryHancock

You can try

 

absolute_column_width='55,14,12,14,12,13,13'

and see if it works for your printer.

 

I ran the program to create the spreadsheet, then manually adjusted the columns to make them fit on a page.  Then I used the CELL function in Excel to see how wide each column was, and used those numbers for the absolute_column_width option on the ODS Excel statement.

 

See Formula Watch: Use the Cell function to check column width, column number, and more for more information about the CELL function in Excel.

 

The screen shot below shows what the report looks like for me when I use the absolute_column_width option.

 

ODS Excel output one page wide.jpgPage Break Preview of ODS Excel output

Ask a Question
Discussion stats
  • 3 replies
  • 138 views
  • 0 likes
  • 3 in conversation