Call a stored process from a stored process

[deleted_user]

A couple of questions:

I have a stored process that is run to create some information. In the basecase, it creates information that is then inserted into a SQL database. I want to be able to call this stored process from another stored process with an additional parameter so that I can just return information to the calling stored process rather than inserting it into a SQL database.

1. How do I call a stored process from another stored process?
2. I intended to return the information in the form of a SAS dataset. Will a dataset in the called stored process, if named the same as a dataset in the calling stored process, overwrite the dataset (in the calling stored process)?

Thanks,
--Charlie

[Cynthia_sas]

Hi:
I'm not sure I understand what you want to do. This sounds more like a scenario for having 2 macros -- one to create your information or subset and then another to do the insert into the database. Or, you could actually have 1 macro program and define a parameter that either did the insert into SQL or the return as a dataset with a PROC PRINT.

Then your stored process would simply call the macro program(s). Depending on the parameters you use in the stored process, you would control the code that got written to the compiler for execution.

About your #2: Generally, once a stored process has finished executing, the WORK library area associated with that stored process is completely gone. The second stored process would not necessarily have any visibility of the first stored process WORK area.

If you used 2 macro calls within a single stored process to control whether the information was written to your data base or to a SAS dataset, the WORK library created in the first macro call would still be available to the second macro call. You might have to explicitly set some macro variables to be GLOBAL macro variables so that you would not have scope issues between local and global symbol tables -- but that is very do-able.

I don't believe there is directly a way to have one stored process call another stored process. Although there are ways to do things like make an HTML page from one stored process with a button or form to invoke the Stored Process Web Application for a second stored process. You might want to consult with Tech Support on this question.

cynthia

[deleted_user]

Hi Cynthia,

Thinking that if I have to define the macro I want to execute in every Stored Process there is no need to set a macro. I had two Questions in mind:
- Can I change the work of Stored Process?
- Or can I Set a macro as Global to be recognized every time I use it even by a stored Process?

Thx
Asma

[Cynthia_sas]

Hi,
There is no need to "define" the macro every time you want to use it. You can create autocall macros and store them in the pre-defined autocall location (under the SASMain/.../Lev1 directory) -- so that they are always available to any stored process to call.

So, for example, if you defined the %PRTDATA macro, which did a simple check of the number of observations and then did a PROC PRINT IF the number of obs was greater than 0, then any stored process could call %PRTDATA (assuming it was stored in an autocall macro location known to the Stored Process server and/or the Workspace server).

I'm not sure I understand what you mean about changing the "work of Stored Process" --do you mean that you want to point the WORK library to a different location other than the default server location? If you want to create datasets that persist, then you could write them to a permanent location on any drive that was accessible and writable from the server you were using. You might have to work with your administrators to set that up, get permissions, etc, etc. But then, it's a permanent library, not a work library -- on the other hand, permanent libraries would be accessible to subsequent stored processes, as long as the stored processes knew how to get that data (your biggest issue here would be whether to register the permanent data sets in the Metadata so they were accessible to other apps on the platform or whether you would just make yourself a library out on the server that was for these datasets that you needed to persist).

As for your last question "can I set a macro as global to be recognized every time I use it, even by a stored process" --
-- if by "macro" you mean macro programs -- yes -- that's the purpose of having an autocall macro library.
-- if by "macro" you mean macro -variables- then the answer is "sort of" -- As long as your SP is running, then no matter how many macro calls you have or how long your program is, the macro variable Global symbol table is available to your SP. Of course, local symbol tables work the same way they work outside of the stored process context - -but then, of course the recommendation here is to use explicit %GLOBAL statements to make sure that your macro variables are put in the Global symbol table and not a local one. Either way, as soon as your SP ends and results come back to the user, your SAS session, its Work library and its Global symbol table are all gone.

Remember that every stored process gets its own "self-contained" SAS session to run in (depending, of course, on load managing and how SAS is started up on the server) Generally, imagine that every SP runs in a little bubble of SAS. So if you had 2 users, each running an SP, each SP gets a separate SAS session on either the Workspace server or the Stored Process server. Both of those users' SPs could call the same autocall macro; each user's SP would get its own SAS session. User1's SP would have no visibility of User2's SAS session and vice versa. As soon as the SPs finish processing, the two separate SAS sessions go away; their work areas are cleaned up; and the server is ready to process a new SP request. If either user wants to execute a new SP, then they would get a NEW SAS session for the NEW SP.

cynthia