08-01-2013 07:38 AM
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
08-01-2013 09:07 AM
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.
08-01-2013 09:18 AM
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.
08-01-2013 09:32 AM
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.