Data visualization with SAS programming

Export Graph to excel

Posts: 0

Export Graph to excel

Dear SAS users,

When I try to export a chart using PROC GRAPH and ods statements, the chart image seen is a small one in excel file which gives poor visibilty of chart. How can I overcome this to get a big (desirable size) image/chart in excel using PROC GRAPH & ods statements?

Thanks for your support,
Grand Advisor
Posts: 9,596

Re: Export Graph to excel

I think you can copy image from WORK catalog ,then can import it to excel file,and
adjust it's size as you need.
Posts: 8,721

Re: Export Graph to excel

You don't say how you are creating your ODS file. However, I am thinking that you must be using one of the ODS destinations that create HTML tags and that you're using SAS/GRAPH code with ODS.

I don't understand what you mean by PROC GRAPH?? Are you using PROC GPLOT or PROC GCHART??? Or, are you using one of the SAS/STAT procedures with ODS GRAPHICS.

When you use ODS HTML destinations, you are not "technically" doing an EXPORT to Excel. Excel will only accept "data" from PROC EXPORT (not a graph). Although, when you have a graph in the WORK.GSEG catalog (if you are using classic SAS/GRAPH, such as GPLOT or GCHART), you can use the SAS/GRAPH editor to export your SAS catalog GRSEG entry to an external file format, such as BMP, PNG, GIF or TIF format so you can insert that image into your Excel worksheet (using the INSERT --> PICTURE from the pull-down menus).

However, using the editor to export to an image from the GSEG catalog is not necessary if you are using ODS HTML. For example, if you run GCHART, you can simply specify HSIZE and VSIZE graphics options when you create the HTML file. Then ODS HTML will create an external image file and will use an <IMG> tag to point to this external image file. (If you are using ODS GRAPHICS statements in your code, there are other options (not HSIZE and VSIZE) to control image size.)

When I use the HSIZE and VSIZE graphic options as shown in the code below and then open the HTML file with Excel, I can see that the size properties of the image are 4" wide and 6" high (right-click on the image in Excel and select the Size and Properties menu item). The external image file created is REG.PNG (REG from the NAME= option and PNG from the DEVICE= option) and the HTML file created is: Open_With_Excel.xls -- and both files will be stored in the c:\temp directory.

Remember that by naming the file extension ".XLS" you are only "fooling" the Windows registry into launching Excel when you double click on the file icon. If you looked inside the file with Notepad, you would see HTML tags that Excel knows how to open and render.

goptions reset=all hsize=6in vsize=4in device=png;

ods listing close;
ods html path='c:\temp' (url=none)
file='Open_with_Excel.xls' style=sasweb;

proc gchart;
where region in ('Asia', 'Africa', 'Pacific');
vbar region / type=sum sumvar=sales

ods html close;
Ask a Question
Discussion stats
  • 2 replies
  • 3 in conversation