Multitab Excel Graph and List report

[tommy81]

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

[RichardinOz]

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

[tommy81]

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.

[RichardinOz]

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 UN...)

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