BookmarkSubscribeRSS Feed
_Altons_
Calcite | Level 5
Hi All,

I'm developing a stored process that will be saved in a Unix Server and in the other hand I have a predefined excel report which has 2 tabs, 'Report' & 'Data' .My SP needs to write out the results into specific cell ranges in the Data tab that will populate all the metrics required in the report tab.

Furthermore, the SP should (would be ideal) sort out the whole process of opening, renaming and saving the spreadsheet (we don't want the user messing around with the original template).

So it seems that my SP needs to behave like a DDE code, but I am not sure whether this can be done.

I haven't done much yet, just looking for some guidelines before embrace my destiny 😉

Any help would be really appreciated.

Regards,

_Altons_

Message was edited by: _Altons_ Message was edited by: _Altons_
2 REPLIES 2
_Altons_
Calcite | Level 5
No answers so far, Is it a tough question?
Cynthia_sas
SAS Super FREQ
Hi:
As far as I know, you cannot put DDE calls into a Stored Process (SP). If you have the SAS Add-in for Microsoft Office, for example, you would create a multi-sheet workbook by opening Excel, running your SP and then choosing where the SP populates in the sheet. To populate a new sheet, you would run a second SP and tell that SP where to populate (new sheet, existing sheet, new workbook).

Your scenario :
My SP needs to write out the results into specific cell ranges in the Data tab that will populate all the metrics required in the report tab.


sounds like the kind of thing that folks do with Excel -- have a Data tab that feeds a report tab. Is it not possible to produce all your metrics -- what's in your report tab with SAS???? If the information from the DATA tab is coming from SAS, there's a very good chance that you don't need it anymore and can go straight to producing the report with SAS -- that's the beauty of the Enterprise Intelligence Platform - you have the ability to create complex analytical reports without going through the whole Data tab/Report tab scenario.


The SAS Add-in for Microsoft Office is .COM technology. There is no automated way, that I know of to make an SP automatically open an existing sheet, populate an existing sheet or automatically save a sheet.

On the other hand, -outside- the world of the SAS Add-in, you still have the ability to run batch jobs and those batch jobs could, theoretically, run programs to create files for the users to open in the morning. However, if the batch job requires user-input (such as to supply parameters), then you may not be able to use batch jobs to create your Excel file.

One thing you might do is consult with Tech Support to see whether they have any other suggestion for what you want to do. At the very least, they could take a look at what's on your Report tab and make some recommendations about how to produce the report with SAS procedures/programs in a SP.

cynthia

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 929 views
  • 0 likes
  • 2 in conversation