Declaring the Library Outside of the Stored Process

[deleted_user]

Hi,

We have built a large numbers of the Stored Processes (SP) with the intention of taking the benefits/advantages it offers. Each of the SP has been designed as like a stand alone macro program where we can just drag and drop the SP to build a complete program. However, one of the issue we face is the ability to pass the library declaration to the related SP.

Let's say below is the SP that we have built:
Example:

* Begin EG generated code (do not edit this line);
*
* Stored process registered by
* Enterprise Guide Stored Process Manager v4.1
*
* =============================================================
*ProcessBody;

%global INPUTFILE
OUTFILE
OUTLIB;

%STPBEGIN;

* End EG generated code (do not edit this line);

DATA OUTLIB.test;
SET myLib.policy;
a = b * c;
RUN;


* Begin EG generated code (do not edit this line);
*';*";*/;run;
%STPEND;

* End EG generated code (do not edit this line);


As you may notice, there is no library being declared within the SP. The reason for this is to avoid the library being declared all over the place that would result difficulty of managing the library especially accidently overwritting the library location with the same libname. Also, the idea is we would like the user to have the flexibility to declare the library of myLib from any unix location that would store the same type of input files that could just be fed into the this particular SP.

Given that is the outcome that we want to archive, alternatively, this is the way we think it would work:

1. Open the EGuide

2.1. Using 'Assign Library' from the Tools Menu Tab to assign a library OR

2.2. Create a new code using File -> New -> Code with the library declaration in the code, example as follows:

LIBRARY myLib '/p81/scratch';

Note that the myLib corresponds to the library name in the SP above.

3. Open the SP above into the project Designer.

4. Link the Library / Code that declare the library to this SP.

5. Run both of the object in the project.

6. Expect the result.


Also, please note that the myLib has never been registered as the metadata by the DI Studio.


Problems:


ERROR: Libname myLib is not assigned.

It seems like the error happened because the SP could the find the myLib library from the metadata server which is reasonable. However, I would have thought that the code/alternative would also be working since it is just matter to declaring the library and I do not understand the SP could only understand the library in the metadata sense.

I may be wrong in using the SP but we are still keen to have the flexibility for the users to declare the library from the location that they want. Can you please advise any alternative or the proper way of doing it?

Thanks,
Jason

[Cynthia_sas]

Hi:
Stored processes (SP) execute on either the workspace server or the stored process server. Neither of those servers has any visibility of an EG session's libnames. Usually, an SP contains libnames for data where the libnames are assigned in the metadata OR that you point to INSIDE the stored process with a LIBNAME statement. (You can also use references to libnames like SASHELP -- where the metadata has been told that these libnames are automatically assigned.)

For example, if you point to data that is registered on the BI Platform and you generate and EG task and then you use the Stored Process Wizard to turn that task into a Stored Process -- somehow the LIBNAME must be dealt with -- if the LIBNAME is automatically assigned and known to the metadata server -- then the stored process wizard will tell you that no LIBNAME is necessary; but, otherwise, EG's SP Wizard has a separate step to make sure that the SP code has a LIBNAME statement -- inside the SP code.

You have 3 choices for dealing with LIBNAMES in stored processes

1) Inside your SP, use a LIBNAME statement with the META engine (and get all the security (read/write/delete controls), etc -- this does mean the library must be registered in the metadata) or

2) Inside your SP, don't use the META engine, but use a LIBNAME statement with the physical server path for the data (in which case, you essentially bypass a lot of the benefits of the metadata, including the read/write/delete controls.) or

3) Register your library and tables in the metadata (using either DI Studio or SAS Management Console) and select the advanced option that the library is automatically assigned or automatically initialized when the server session starts -- this means you would not need a LIBNAME statement inside the SP. Thus, your LIBNAME would become just like SASHELP -- automatically assigned.

Note that none of these 3 options allow for operation as you describe, where MYLIB is picked by the user in the EG session and then the SP uses MYLIB from the EG session, without MYLIB being registered in the metadata.

Your design of dragging and dropping the SPs in an EG project sounds interesting. Without seeing ALL the code involved, it is hard to speculate on how you might change your concept or your code a bit to make things work. It almost sounds to me like you're combining some features of autocall macros or %INCLUDE or custom tasks or code nodes with Stored Processes.

Your best resource on how data access works on the BI Platform is to read the Platform Administration Guide about data:
http://support.sas.com/documentation/configuration/bicag.pdf

You might also consider contacting Tech Support for more help with the overall design of what you are trying to do or with specific suggestions for how to make your Stored Processes flexible with regard to different users' libraries. Also, keep in mind that if each of your stored processes is a "stand-alone" stored process with separate
%STPBEGIN/%STPEND statements, then the WORK library from one stored process will not be available to a subsequent stored process unless you do something to explicitly persist the session on the server.

cynthia

[deleted_user]

Hi Cynthia,

First of all, thanks for the reply and the suggested solution. Appreciate that.

Secondly, Yes you are right that we are sort of combining macros with the SP. There are two main ways that we designed the SP, which basically we call them as either autocall SP or non-autocall SP.

Autocall SP basically could have macros in the SP, with the macro calls at the end of the code to execute the program whenever the SP is called, or without any macros in the SP, which would just run whenever the SP is called.

While the non-autocall SP is designed with macros code inside the SP but without the macros call inside the SP. These SP basically are more like a lookup program that needs to retrieve data based on some business rules and they are the lookup programs that can always be used on various and different tables. Hence, these SP could then be even called within other SP.

With the desires of archiving the drag and drop solution we want to archive, the SP therefore have been designed to be really flexible with lots of the things being parameterised, including the library. And of course, documentation of explaning how one SP could connect to another SP is important so that users can understand how to use them in the right way.

One thing to note is, we have tried to %include the code generated by these SP into the DI Studio (in that way, we centralise all the coding into single source) and codeWright (programs editor that we use) and they just work perfertly in the way we desire. We never have problem with the library even it is not regiestered within the metadata. We obviously like to see this happen in EGuide to so that we can transit more people (especially the business people who knows littble about the coding) from the Base SAS into the EGuide.

FYI, I once spoke to one of the SAS consultant that currently on site with us, and he suggested to use some codes to directly write the metadata of the library into the Foundation or Project repository, which are proved to work. However, that seems to be a bit scary and dangerous to me as not all the users are comfortable and most importantly familiar and understand how it works in the background. This would definitely increase the potential the corruption of the metadata server, that I want to avoid at any cost.

At the same time, I have also spoken to the Tech Support which I am still waiting for the answer.

Thanks again Cynthia.


Regards,
Jason