ODS Excel Chart Out of Scale/Distorted

[michellekorm]

Hello, 

I ran the code from the blog post "Experimenting with ODS EXCEL to create spreadsheets from SAS," In excel, the chart is out of scale/distorted. I'm running SAS 9.4 (TS1M2).

I opened the excel document, right clicked on the image, and saw that, while the original size height and width match the ods graphics options, the scale height and scale width are set to 98% and 69%, respectively (image below).

Does anyone know why this is happening or how to fix the scale?

SAS code from 

http://blogs.sas.com/content/sasdummy/2014/08/29/experimenting-with-ods-excel-to-create-spreadsheets...

I ran it with and without the ods graphics options, with similar results.

ods excel file="c:\projects\output\example.xlsx" 
 /* will apply an appearance style */
 style=pearl
 options(
  /* for multiple procs/sheet */
  sheet_interval="none" 
  /* name the sheet tab */
  sheet_name="CARS summary"
 );
 
/* add some formatted text */
ods escapechar='~';
ods text="~S={font_size=14pt font_weight=bold}~Cars Summary and Histogram";
 
/* tabular output */
proc means data=sashelp.cars;
var msrp invoice;
run;
 
/* and a graph */
ods graphics / height=400 width=800 noborder;
proc sgplot data=sashelp.cars;
histogram msrp;
run;
 
ods excel close;

Scale height and scale width are set to 98% and 69%, respectively. 

Thanks,

Michelle

[michellekorm · Accepted solution]

I contacted SAS Technical Support. We found two workarounds for this issue. 

1. The SAS workaround is to set the absolute_column_width value in the ods excel options. Setting style=pearl and absolute_column_width="11.7" results in an image with close to 100% scale width in excel. However, setting style=journal requires absolute_column_width="10.75" to produce an image with 100% scale width in Excel. You must include a non-graph table and a graph in the same worksheet in order for the absolute_column_width option to have an effect on the default size/scaling of the graph.  If the worksheet *only* includes graph output, then the  absolute_column_width  does not have any effect on the default size/scaling of the graph. 

Example:

ods excel file="c:\projects\output\example.xlsx" 
 /* will apply an appearance style */
 style=pearl
 options(
  /* for multiple procs/sheet */
  sheet_interval="none" 
  /* name the sheet tab */
  sheet_name="CARS summary"
  absolute_column_width="11.5"
 );

2. The Excel workaround is to right click on the image to format the picture, and under size properties click the "reset" button. You can use Excel VBA to scale the picture if you have multiple affected images and/or worksheets.