Hi:
A stored process generally runs against data that is defined in the metadata, data that lives on a server and has security constraints defined in the metadata.
The stored process program lives in a source code repository that the servers know about and no matter where you run your stored process -- the Workspace server versus the Stored Process server -- usually neither of those servers has visibility down to your local machine. And, when you write your stored process, there's not a way to point the stored process to a data source that lives on your local machine. So...right now, you should be sensing a theme here --
stored process = server
The fact that you can run tasks in Excel on a local data source makes it seem like EVERYTHING happens on your local machine, but this is not true. You can run tasks on your local machine because the tasks, (what you can do in the SAS Add-in for Excel) are .NET plug-ins, that are working down on the local machine and are communicating with the servers via a special connection behind the scenes -- if the communication is taking place with the tasks, you don't see the communication to the server happening--except as a bit of lag time.
But, when you make a request to run a Stored Process -- everything happens up at the server level -- data, program code, processing -- only the results of the Stored Process come back to your local machine.
You might consider keeping this particular workbook up on a server that was known to the Workspace server or Stored Process Server, for example. Then your programmer could code a stored process with the logic necessary to read the Excel workbook into SAS format and run the programming steps against this version of the workbook.
If, on the other hand, you want someone to open a workbook on their personal machine and perhaps modify the workbook and then run a stored process against what they've just changed -- I think this scenario will NOT work -- unless they save their workbook to a server location that's known to the stored process using the name that the stored process is expecting to find on the server.
I don't know whether this possibility (aiming a SP at a local file) is going to be possible in future releases of the SAS Add-in. That's a question for Tech Support -- They could find out whether it's technologically possible or maybe planned as an enhancement for a future release of the BI Platform.
To answer your second question...about whether it's possible to "have a interface where we can choose what SAS program to run depending on the criteria that will be set in excel."
That interface is called the SAS Macro facility. If, for example, you give the users an input parameter choice of running a weekly or monthly report AND if you have a weekly macro program called %WEEKLY and a monthly macro program called %MONTHLY (and the programs were stored in your AUTOCALL macro location in the BI Platform), then your stored process author could code something like this in the stored process program or in one of the macros that will be executed. Let's say you called the input parameter REPTYP:
[pre]
%if &reptyp = WEEKLY %then %do;
%WEEKLY;
%end;
%else %if &reptyp = MONTHLY %then %do;
%MONTHLY;
%end;
[/pre]
The code would be more involved than the snippet I've shown and if you are not familiar with SAS macro programming, you should not attempt to just insert this snippet into a SAS program -- %IF statements cannot run in open code and need to be inside a macro program of their own -- so the design of your stored process will become more complex.
Reading more about SAS macro programming and SAS programming in general will help you design good stored processes.
cynthia
