BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
michellekorm
Calcite | Level 5

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. 

 

Capture.PNG

 

Thanks,

Michelle

1 ACCEPTED SOLUTION

Accepted Solutions
michellekorm
Calcite | Level 5

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. 

View solution in original post

1 REPLY 1
michellekorm
Calcite | Level 5

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. 

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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
  • 1 reply
  • 2077 views
  • 1 like
  • 1 in conversation