ODS Excel GRAPHS

twildone · Posted 08-20-2016 09:59 AM

Hi.....I am creating an excel file with tables on different worksheets/tabs using Proc Report with ODS ExcelXP. I want to include graphs that are created and correspond to each table and placed them beside the table. Any suggestions. Thanks.

Reeza · Posted 08-20-2016 10:17 AM

Your subject is ODS EXCEL but your question mentions ExcelXP, which one is it?

ExcekXP does not support graphs.

Cynthia_sas · Posted 08-20-2016 11:05 AM

Hi:
Reeza is correct. ODS TAGSETS.EXCELXP does not support graphs, but ODS EXCEL does. Your choices are to use ODS EXCEL and create graphs with SAS procedures. Check out the pre-production PROC MSCHART: http://support.sas.com/documentation/cdl/en/odsproc/67922/HTML/default/viewer.htm#p1rkgggqswgaezn158...

cynthia

twildone · Posted 08-20-2016 12:01 PM

Hi Cynthia....I am using SAS 9.2 and I don't think PROC MSCHART can be used with SAS 9.4...

twildone · Posted 08-20-2016 12:00 PM

Hi Reeza....Its ExcelXP. I need to and want to export the graphs beside the tables in the excel file. Whats the easiest and best way to do this?

Cynthia_sas · Posted 08-20-2016 01:06 PM

Hi -- By Microsoft design specifications, the type of Spreadsheet Markup Language produced by ODS TAGSETS.EXCELXP does NOT support graphs.

That means you will have to 1) create your .XML file using TAGSETS.EXCELXP and then 2) explicitly save the file as a .XLS or .XLSX file so it is in native Excel format and then 3) insert the graphs either manually or using VB script or using SAS and DDE (which I don't recommend).

You are correct that you can't use MSCHART with SAS 9.2, but Reeza is correct that you can't use graphs with ODS TAGSETS.EXCELXP. So you will NEVER be able to use SAS to insert graphs into the TAGSETS.EXCELXP output using ONLY TAGSETS.EXCELXP -- Microsoft did not include graphics support in Spreadsheet Markup Language and that means SAS can't do something that Microsoft does not support.

Investigate the HTMLPANEL destination, TAGSETS.TABLEEDITOR or TAGSETS.MSOFFICE2K, all of which are HTML-based destinations. You can insert links to graphs in HTML files and Excel has been able to open HTML files ever since Office 97.

cynthia

Reeza · Posted 08-20-2016 05:24 PM

@twildone wrote:

Hi Reeza....Its ExcelXP. I need to and want to export the graphs beside the tables in the excel file. Whats the easiest and best way to do this?

9.2 was released in 2008 so it's only about 8 years old. You can't expect 2016 result type reports from an 8 year old version.

Upgrade to 9.4. It's free and included with your license.

Given that that it can be accomplished relatively easily in newer versions its not worth the time developing something for 9.2, unless you're being paid for it 🙂

twildone · Posted 08-25-2016 07:28 PM

Hi Reeza.....I have upgraded to SAS 9.4...hopefully I can figure out what is needed and should be done from ExcelXP in SAS 9.2 to EXCEL using SAS 9.4.