07-20-2011 01:33 PM
Not sure where to place this question and not sure if it is even possible.
I have multiple sas programs that execute various reports which get exporting into excel. Some with a simple export statement and some using DDE because the data needs to start on specific lines.
Some times these reports are exporting to excel at the same time!! Ouch This causes problems. Is there another way to create an excel sheet in sas? The excel files are reports for usage by other departments.
If any other info is need just ask, I am clueless
07-20-2011 01:47 PM
I thought SAS kinda went sequentially and did things as you went along.
Is the DDE running into itself, or the proc export and the DDE...I sort of assumed the DDE wouldn't start until the proc export finished, or do you have multiple jobs going at the same time?
You can look into the sleep(5) or some other time to allow DDE time to finish the process before continuing or just in general.
07-20-2011 02:07 PM
I have multiple SAS Job running. Most of my reports have to be out by 7am before the start of business. The data does not finished uploading until 5 am which give almost 2 hours to get all the report out. Some days kicking off several SAS Job does not cause a problem other days when there is problems with the data upload everything crashes.
One SAS Programs is open and creating 3 different excel reports
another SAS is open and kicking of all items in a bat file
while another SAS is open executing more reports
Yes it might not be a good idea to have 3-7 SAS sessions open especially when 3 or more create excel sheets but currently we created reports with VB, SQL and SAS. So yes even my SQL jobs crash when everything starts exporting at the same time.
Just trying to find a better way to create excel reports Simultaneously.
07-20-2011 02:27 PM
Could you structure the reports to write to independent files so that they will not interfere with each other.
If you still need the combined files then create a step (pehaps in VB) that combines the files that runs when everything is finished. That step should be fast as it is not doing any real calculations.
07-20-2011 02:31 PM
All the jobs write to different excel or report files.
The problem is that SQL and SAS DDE both open up excel to transport data. If multiple SAS jobs are exporting with DDE or if SQL has an excel sheet open then the job bomb out.
07-20-2011 02:33 PM
if I use only a bat file for all the programs that use dde then some of the report are late getting out. The simple solution is to have another computer to execute jobs but that is not possible at this time. So I am just trying to figure out a work around.
07-20-2011 03:41 PM
Would it be possible to have all the files created that needed exporting and then do the following:
Create a dataset that contains the list of files that should be exported.
Export files 1 by 1 calling whatever program.
As each export runs then update your list with a complete if success, failed if not using some _error_ variable to check for errors.
Check for the preceding job to complete to run.
The dataset above should have the following format
export_job date status
export1 01Jul2011 complete
dde1 01Jul2011 complete
export2 01Jul2011 complete
dde2 01Jul2011 complete
Basically separate the export process to a separate section...
Another option is to write your data to CSV or another data source and have your excel reports pull data from that common source. So when the source gets updated the excel report will ask to be updated when reopened.
I don't know if any of that will work for you, it sounds like you have a really short timeline to run a large set of reports...