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

SAS INNOVATE 2024

Innovate_SAS_Blue.png

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

Register now!

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.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

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

View all other training opportunities.

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