topic Re: Change Excel Print Scale in SAS in ODS and Base Reporting

Change Excel Print Scale in SAS

djbateman — Mon, 06 Nov 2017 22:30:11 GMT

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?

Re: Change Excel Print Scale in SAS

Reeza — Mon, 06 Nov 2017 22:31:01 GMT

Do you have Fit to Page set?

Re: Change Excel Print Scale in SAS

Ksharp — Tue, 07 Nov 2017 12:31:11 GMT

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

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

Re: Change Excel Print Scale in SAS

djbateman — Tue, 07 Nov 2017 14:03:10 GMT

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".

Re: Change Excel Print Scale in SAS

djbateman — Tue, 07 Nov 2017 14:04:56 GMT

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.

Re: Change Excel Print Scale in SAS

Reeza — Tue, 07 Nov 2017 16:10:42 GMT

Post your full code please.

Re: Change Excel Print Scale in SAS

djbateman — Tue, 07 Nov 2017 16:13:58 GMT

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(&amp;L) Current : %cmpres(&currf) &#10; Previous : %cmpres(&prevf) &#10; %cmpres(&pgmdt) &#10; New Term = New/modified since ‘Previous’ date &#10;
                %nrstr(&amp;R) Page: %nrstr(&amp;P) of %nrstr(&amp;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;

Re: Change Excel Print Scale in SAS

Reeza — Tue, 07 Nov 2017 16:20:42 GMT

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'

Re: Change Excel Print Scale in SAS

djbateman — Wed, 08 Nov 2017 14:25:33 GMT

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?

Re: Change Excel Print Scale in SAS

Reeza — Wed, 08 Nov 2017 15:55:12 GMT

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?

Re: Change Excel Print Scale in SAS

djbateman — Wed, 08 Nov 2017 17:05:33 GMT

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.