The SAS Output Delivery System and reporting techniques

Change Excel Print Scale in SAS

Reply
Regular Contributor
Posts: 239

Change Excel Print Scale in SAS

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?

Super User
Posts: 20,731

Re: Change Excel Print Scale in SAS

Posted in reply to djbateman

Do you have Fit to Page set?

Regular Contributor
Posts: 239

Re: Change Excel Print Scale in SAS

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.
Super User
Posts: 10,210

Re: Change Excel Print Scale in SAS

Posted in reply to djbateman

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

 

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

Regular Contributor
Posts: 239

Re: Change Excel Print Scale in SAS

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".
Super User
Posts: 20,731

Re: Change Excel Print Scale in SAS

Posted in reply to djbateman

Post your full code please.

Regular Contributor
Posts: 239

Re: Change Excel Print Scale in SAS

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;
Super User
Posts: 20,731

Re: Change Excel Print Scale in SAS

Posted in reply to djbateman

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'

 

Regular Contributor
Posts: 239

Re: Change Excel Print Scale in SAS

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?

Super User
Posts: 20,731

Re: Change Excel Print Scale in SAS

Posted in reply to djbateman

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?

Regular Contributor
Posts: 239

Re: Change Excel Print Scale in SAS

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.
Ask a Question
Discussion stats
  • 10 replies
  • 187 views
  • 0 likes
  • 3 in conversation