Running stored procedure from ms excel

[Elliott]

I have a stored process I created and am trying to execute from excel.  When I go to the sas tab in excel and try to browse for the sp I cannot locate it.  Is there a specific place on the server where the sp needs to be stored so that I can browse to it?  My only option is sas folders, but it does not give me that path on the unix server where that folder is.  Is there a way to browse to the location on the unix server where I do have the sp stored.  

any assistance you can offer will be greatly appreciated.

Thank you,

Elliott

[PatrickCuba]

Hi Elliot,

When the Stored Procedure is created you need to take note of what the Metadata Path is. Or you can check the existing Stored Procedure's properties.

Then you need to make sure that the Stored Process is loaded to an area where the user using "Excel -> SAS" will have access to.

Make sure that the Metadata credentials you use to access the SAS metadata server from Excel is the same as where the Stored Procedure is stored (server name port etc.)

Once that has been confirmed then you need to use the "Reports" button under the SAS tab in Excel, navigate to where the Stored Procedure is saved and open it. 

Next you need to decide where you want to save the results, either a New worksheet, New workbook or an Existing worksheet with tabular coordinates. When you run the Stored Procedure you may be prompted for inputs or not depending on how the Stored Process has been designed.

[Elliott]

Patrick,

Thanks for the info, unfortunately it is not helping.  I path that is provided in excel when I am connected to the correct server is no where I can find on the actual server.  I don't have the ability to browse from any place on the SAS tab in excel to the location on the server were the sp is actually stored.   I am not sure I even have access to the sas metadata location on the server (which may be why I cannot browse to the actual location). 

I have tried going to the Reports button but that only lets me look for the reports file type not the procedure with the .sas extension that we have created.

What we are trying to do is this.  I have morning reports.  I review 1 and when I am finished I want to execute the next program in the process that will load the data I just approved.  We are not using the excel data in either program, just wanted to avoid me sending someone else an approval then they in turn have to go out and run a sas program to complete the process.  I just wanted to be able to execute from my excel report.  hope that makes sense.

Elliott

[PatrickCuba]

1. You really need to chat to the SAS administrator to check your access

2. Stored Procedure can be created using SAS Enterprise Guide and stored in Metadata or on disk. Most administrators want it in metadata because it is easier to manage. 

3. Once it is stored in Metadata it does not have a .sas extension and it can run by anyone who has permission to it.

[keane76]

 Hi Elliott,

I think you are looking in the wrong location for your stored process. Please make note of the following points when you are storing your stored process. When you saving your sas program as stored process, you use the "create new stored process" wizard, for specifing the location of the stored process as part of metadata. So in the wizard, when you have to mention the Location: value in Name and Description screen, we need to point to a location in metadata not a unix or windows or any local server location. Only when you save it as part of metadata, you will be able to access the stored process from your excel application.

Next when you mention the Execution options in the third screen, for the Source code location and execution: value choose the radio button Allow execution on other application servers(store source code in metadata).

Once you have set these options correctly, you should now look for the path mentioned in the first screen as Location: value in your excel application, you should be able to see your stored process there.

Attached are some screens to guide.

[Elliott]

once I have done this...Next when you mention the Execution options in the third screen, for the Source code location and execution: value choose the radio button Allow execution on other application servers(store source code in metadata).


Will my other team members be able to see the stored process? It is stored in the metadata but in a folder under my name directly and so far no one has the ability to view that folder data, they can browse to it, just not see anything in the folder. This was a pre-deteremined destination in the metadata.


Thanks

[Timmy2383]

By default other users won't be able to see that.  If you create metadata objects that you want to share with other groups/users then it's probably better to save it in a line-of-business/group specific metadata folder (my recommendation) or under default "/Shared Data".

[keane76]

Hi Elliott

We were actually working on how to get the stored process to be accessble from MS Excel. Did you give it a try after making the changes i had suggested.

As to other users being able to view and use your stored process, as Timmy2383 has suggested you should probably see if the metadata folder location where you have saved your stored process has the necessary privileges for other users to be able to view it.

[Elliott]

I am able to run my stored processes from excel.


The problem is my team members cannot see my sp's.. Ihave reached out to our admin to create a shared folder as suggested.


Thank you,


My next question is.. how do I get a log to generate that I can review?


Cindy

[keane76]

Hi Elliott

If you are accessing your stored process from within a web page through excel, then may be you  can try doing this:

http://bi-notes.com/2012/12/sas-stored-process-debugging-from-the-url/