topic multiple exporting to excel in Microsoft Integration with SAS

multiple exporting to excel

mick_g — Wed, 20 Jul 2011 17:33:53 GMT

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.

The problem:

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

multiple exporting to excel

Reeza — Wed, 20 Jul 2011 17:47:12 GMT

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.

multiple exporting to excel

mick_g — Wed, 20 Jul 2011 18:07:56 GMT

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.

multiple exporting to excel

mick_g — Wed, 20 Jul 2011 18:09:16 GMT

I thought that maybe I could create some of the reports with XML but I have never worked with it. So I don't know what it can do.

multiple exporting to excel

Tom — Wed, 20 Jul 2011 18:27:44 GMT

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.

multiple exporting to excel

mick_g — Wed, 20 Jul 2011 18:31:32 GMT

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.

multiple exporting to excel

mick_g — Wed, 20 Jul 2011 18:33:42 GMT

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.

multiple exporting to excel

Reeza — Wed, 20 Jul 2011 19:41:38 GMT

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