BookmarkSubscribeRSS Feed
MaryHancock
Calcite | Level 5

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;

3 REPLIES 3
SuryaKiran
Meteorite | Level 14

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
MaryHancock
Calcite | Level 5

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.  

SuzanneDorinski
Lapis Lazuli | Level 10

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 outputPage Break Preview of ODS Excel output

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 1302 views
  • 0 likes
  • 3 in conversation