topic Re: Multitab Excel Graph and List report in SAS Programming

Multitab Excel Graph and List report

tommy81 — Thu, 01 Aug 2013 11:38:22 GMT

Hi ,

I`m trying to create an Excel based report which has List reports on Sheet 1 followed by 6 graphs on subsequent sheet.Sheet 1 feeds the graph.

Tried the following methods :

SAS DDE: Since the destination file is in UNIX this method did not work

SAS EXCEL XP: It does not support GPLOT for line graph

Has anyone tried automation of such a report.Kindly suggest alternative

Re: Multitab Excel Graph and List report

RichardinOz — Thu, 01 Aug 2013 13:07:33 GMT

If your data ranges are always the same, even if the list data is refreshed, just create the charts in Excel. They will update when the list data is refreshed.

If there are parameters which need to control how the charts are displayed (eg titles) you may be able to define and export those to a hidden tab, to be accessed in the chart.

Richard

Re: Multitab Excel Graph and List report

tommy81 — Thu, 01 Aug 2013 13:18:07 GMT

Data ranges are always same.

The issue is will I be able to update an excel file kept in UNIX/SOLARIS env using SAS DDE Method.

If possible kindly provide a sample code .

I tried to run the normal DDe code and it returemd Invalid Device type.

Re: Multitab Excel Graph and List report

RichardinOz — Thu, 01 Aug 2013 13:32:51 GMT

DDE is specific to the Windows environment.

You will have to use proc export to export your list data

Be aware that there is a bug in SAS 9.1.3 - fixed in 9.2 (20325 - Using the EXPORT procedure with DBMS=XLS in 9.1.3 SAS/ACCESS® Interface to PC Files under UNIX to create a Microsoft Excel file might cause problems with the Excel file)

In the past (in Windows) I have had to fiddle with tab names to make export work - SAS often created a new tab instead of updating the existing one. Best idea was to let SAS create the list tab then open the workbook in MS Excel to create the charts for the first time.

Another approach, if you have a Windows PC with a full Base SAS installation and SAS connect in both environments is to use the Windows PC as the server (rsubmit from UNIX, opposite to the usual config). You should be able to rsubmit code using DDE or a windows libname to update your workbook (which has to be open in Windows for DDE to work) and do the update that way, if necessary then uploading the finished workbook to your UNIX server.

Richard