topic Re: ods excel How can I control which columns get printed on a page? in ODS and Base Reporting

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

MaryHancock — Wed, 13 Jun 2018 17:49:34 GMT

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;

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

SuryaKiran — Wed, 13 Jun 2018 18:48:11 GMT

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-data-AKA-generate/ta-p/258712

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

MaryHancock — Wed, 13 Jun 2018 20:03:10 GMT

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.

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

SuzanneDorinski — Thu, 14 Jun 2018 00:10:24 GMT

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.

Page Break Preview of ODS Excel output