BookmarkSubscribeRSS Feed
djbateman
Lapis Lazuli | Level 10

I have a couple of reports that I am exporting to Excel.  They are almost identical size wise (same number of columns with same widths).  However, when I print preview the files, one is scaled 73% and the other is scaled 49%.  I can't figure out why it does this or how to change it in the code.

 

I do have this snippet in my code, but it doesn't seem to have an influence here:

 

options TopMargin = 1.25in
        BottomMargin = 1.24in
	LeftMargin = 0.08in
	RightMargin = 0.08in;

Any suggestions?

10 REPLIES 10
Reeza
Super User

Do you have Fit to Page set?

djbateman
Lapis Lazuli | Level 10
Where do I add that? Is it just in an option statement? In the PROC REPORT statement? I have searched for it but can't find where it goes.
Ksharp
Super User

I am not sure if it could work, try it.

 

proc report ......  style={ output=100% };

djbateman
Lapis Lazuli | Level 10
Thanks for the suggestion, but that didn't seem to work. I reran with that added, and when I attempted to open the file, I got a message "File cannot be opened".
Reeza
Super User

Post your full code please.

djbateman
Lapis Lazuli | Level 10

This is just the section that creates the report.  The second report is identical code but referencing a different dataset.

 

options noxwait noxsync mprint mlogic symbolgen noquotelenmax
 		orientation=landscape
		TopMargin = 1.25in
		BottomMargin = 1.24in
		LeftMargin = 0.08in
		RightMargin = 0.08in;

ods listing close;
ods tagsets.excelxp file="S:\cdm\Programming\&compound.\&study.\UTR\&protocol._MedDRA_UTR_&currf..xml"  style=statistical;
ods tagsets.excelxp options (row_repeat = 'header'
				frozen_headers = '1'
				autofilter = 'yes'
				orientation = 'landscape'
				center_horizontal = 'yes'
				fittopage = 'yes'
				pages_fitwidth = '1'
				pages_fitheight = '500'
				absolute_column_width = '6,7,7,20,15,15,15,15,15,15'
				autofit_height = "yes"
				sheet_name = "MedDRA UTR"
				embedded_titles = 'no'
				embedded_footnotes = 'yes'
				wraptext = 'yes'
				gridlines = 'yes'
print_footer = "%nrstr(&L) Current : %cmpres(&currf) 
 Previous : %cmpres(&prevf) 
 %cmpres(&pgmdt) 
 New Term = New/modified since ‘Previous’ date 

                %nrstr(&R) Page: %nrstr(&P) of %nrstr(&N)" );

title1 j=l "Vertex Pharmaceuticals Incorporated";
title2 j=l "Protocol &protocol.";
title3 j=l "MedDRA Unique Terms Report - %cmpres(&vmeddra.)";
title4 j=l "Count of terms not coded:  %cmpres(&all_terms.)";

proc report data=all center nowindows headline headskip spacing=1 split='#' missing style(header)={background=lightyellow font_weight=bold foreground=black};
	column flag dsname rec_count term1 llt_name ptname hlt_name hlgtname bodsys comments;
	define flag / display center "New Term " flow;
	define dsname / display center "DataSet " flow;
	define rec_count / display center "Record Count" flow;
	define term1 / display center "Verbatim " flow;
	define llt_name / display center "Lowest Level Term" flow;
	define ptname / display center "Preferred Term " flow;
	define hlt_name / display center "High Level Term " flow;
	define hlgtname / display center "High Level Group Term" flow;
	define bodsys / display center "Primary System Organ Class" flow;
	define comments / display center "Report Review Comments" flow;
run;

ods tagsets.excelxp close;
ods listing;
Reeza
Super User

You do have fittopage set, so that will scale each page to fit it on and it will differ because the data differs.

 

fittopage = 'yes'

 

djbateman
Lapis Lazuli | Level 10

Reeza,

 

I'm not sure how fittopage is supposed to work.  I made sure that both of my files have identical total widths.  I have even attached screenshots for you to see how the print preview comes out.  The MedDRA file completely fills up the page while the WHODDE file still has wide left and right margins.

 

The MedDRA file has 10 columns:  absolute_column_width = '6,7,7,20,15,15,15,15,15,15'

The WHODDE file has 11 columns:  absolute_column_width = '6,7,7,15,15,10,15,15,10,15,15'

 

Both files have an absolute_column_width of 130.  Why don't they fill the page the same way?

Reeza
Super User

Fit the Page is an Excel setting that basically tries to scale the data to 'fit a single page' for whatever page you have set up.

 

Setting both the absolute size and fittopage doesn't make sense in that respect. If you set it to NO, does it work the way you expect?

djbateman
Lapis Lazuli | Level 10
Nope, that doesn't help either. Maybe I just need to tell the recipient of the reports to manually scale the shrunken report to fit the page before printing. I would like to automate it, but not everything can be automated.

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
  • 10 replies
  • 1422 views
  • 0 likes
  • 3 in conversation