Using a pure excel worksheet as the active data source in a stored process

[DJWanna]

I am trying to create a stored process where it would use the active data within the excel worksheet as the source. I created a dummy file and used the task for one way frequencies, for example, copied the code and created a stored process. I then tried to run the stored process and it produced an error:

48 +PROC SQL;
49 + CREATE table WORK.SORT as
50 + SELECT ThisOne FROM WORK._EXCEL_;
ERROR: File WORK._EXCEL_.DATA does not exist.

(I changed the create view to create table so I could get the error in this step)

Is it possible to create a stored process and have it execute this way?

Thank you.

[Cynthia_sas]

Hi:
Generally speaking, running a task against your active data source is different than running a stored process. 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. A task can run against a SAS data source that you can open into a worksheet in Excel or against data the you pull into Excel or even against an Excel worksheet that you open and want to run a task against.

The stored process lives in a source code repository and no matter where you run your stored process -- the Workspace server versus the Stored Process server -- neither of those servers has the same kind of visibility of your local machine as the tasks do.

This is because the tasks, like EG, or like 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. But, when you make a request to run the Stored Process -- everything happens up at the server level -- only the results of the Stored Process come back to your local machine.

By the time your stored process executed, any WORK session that may have had WORK._EXCEL_ (from the task) was probably already gone. It may have even been on a different server than the one where your stored process was executing.

I don't know whether there's any workaround for this (if you uploaded the Excel file to a server that was known to the Workspace server or Stored Process Server, for example.) That's really a question for Tech Support. If it's not possible in this release of the BI Platform, they could find out whether it's technologically possible or maybe planned as an enhancement for a future release of the BI Platform.

cynthia