Your SAS programs, embedded in web apps and elsewhere

How to call a stored process within another stored process

Reply
New Contributor
Posts: 4

How to call a stored process within another stored process

Hello,

I have a piece of code i call a lot in my SPs and ideally would put it in a .sas program and call it using %include in the SP.

However this isn't possible due to permission restrictions, so i thought about putting that frequently called code in another SP. My problem is, how to call an SP inside another SP as you would an %include?

Any ideas?

FYI, these are web app stored processes, don't know if that makes it any different, but anyway...

Thanks a lot

Alex

PROC Star
Posts: 1,236

Re: How to call a stored process within another stored process

Hi,

I don't think you can have a stored process call another stored process, at least not in the way you are describing.

If you have a module of SAS code you want to reuse, I think making it a %include module, or an autocall macro, are both good solutions.

Maybe you could elaborate on the permissions issues that prevent that possibility.  Is the problem that you don't have write permission to put code somewhere that the stored process server can see it?   Or are you worried about your stored process user needing to have read access to the code? or....?

--Q.

New Contributor
Posts: 4

Re: How to call a stored process within another stored process

my problem was lack of reading permissions from the SP server to wherever i could write a piece of code to include.

I've solved that now, but my main question remains because i could still use calling a SP from within another SP.

It's a bit of a long read, but i appreciate your patience:

I have this SP web app where a bunch of Quality Agents have to check some 4k bank accounts to see if the bank has all the required documentation from the account holders of each account.

So in the first screen a Quality Agent is presented with a random list of 20 bank accounts from the 4k+ that have to be checked and he clicks on one, which takes him to the second screen where there’s a list of documents and he has to say if the bank is in possession of that document or not for the account he just chose. There are 3 radio buttons after each document – yes, no, not applicable and he must choose one for each document that’s required. (for example: account holder’s proof of address – he chooses yes if the bank has that document, no if the bank doesn’t have that document and NA if it’s not applicable for whatever reason)

My problem is this – what if two agents might choose the same account at the same time? The way I took care of it was: when agent A chooses account 123, I’ll insert that account in a temporary table so that if another agent B chooses that same account, I’ll check on the temporary table if the account 123 is there already and if it is, I’ll tell agent B, sorry, that’s being checked already, go back and choose another one.

But then I get a complication - what if, for whatever reason, agent A doesn’t carry out the check until the end? His browser might crash, he might click “back” on the browser, he might close the browser, etc before actually submitting his check. In this case I want to go to the temporary table and delete account 123 so that another agent might pick it and check it, otherwise nobody is ever going to be able to pick up account 123. The way I solved this was with the onbeforeunload HTML method. But because this is HTML, how do I say to onbeforeunload something like delete * from temporary_table where account = 123? The way I’m doing it is - on the function called by onbeforeunload I’m opening another window which is also a web SP

           put "window.onbeforeunload = function(e) { ";

                put "window.open( '&sp_that_deletes_from_temp_table');";

                put "};";

which actually opens a window, does what it has to do (delete * from temporary_table where account = 123) and then closes itself.

But this is very ugly! The user is in the middle of the documents check, decides to not complete it, closes the browser window and suddenly another window opens and closes almost immediately after!!?? Very messy.

What I wanted here was to call an SP that didn’t open any windows (I tried but then got a new window anyway with the error that the SP didn’t have any output, which was precisely what I wanted, but SAS complained about it) and just did what it had to do (delete * from temporary_table where account = 123) quietly.

In summary: what I want is a way to call a SP within another SP to do the cleanup, or ultimately, solving my concurrency problem.

Thanks for your help!

Alex

PROC Star
Posts: 1,236

Re: How to call a stored process within another stored process

Interesting.  I don't have a solution to your specific question.

But an alternative would be to change the architecture somewhat.  For example, when an agent is assigned 20 bank accounts, just treat them as being assigned to that agent.  Don't assume they are done.  Then when an agent confirms that the documentation is complete for an account, update that record to say it is now complete.

That way instead of trying to catch an error (back arrow or browser crash) and delete a record from your table,  you wait for successful completion and then update the status to having been processed.   And keep it as a permanent table, so you could track: which agents checked which accounts, how long it takes a check an account, etc etc.  And if an account has been assigned to an agent but status is "open for checking" for more than a day (or more than 15 minutes, whatever) you could send a reminder, or put it back into the pool to be available to be assigned...

I gues the down side would be the delay in time from a browser crash until the account is put back in the pool.

--Q.

New Contributor
Posts: 4

Re: How to call a stored process within another stored process

Thanks Quentin.

The scenario where an agent is assigned a set of X accounts can't be. It is a requirement that any agent should be able to pick up any account, but I like your suggestion of keeping it in a table for some time and after that time returning it to the pool of accounts.

However, there's still another instance where calling an SP within an SP would be great: In my web app I have a reports area. Users can come in and click on reports, the report runs and shows the user the final result. This works fine for reports that run fast but if a report takes an hour to run, I'd like the user to just kickstart it and go away do whatever and come back after the report had finished (the app could send an email with the report attached or informing the report is available or something), instead of how it is currently: clicking the link to call the SP that builds the report and then waiting there for an hour or whatever for the report to build.

(one obvious solution would be to run it everyday anyway and when the user wanted it, it would already be run and he just had to query the final table, but i wouldn't want to run every day reports that might only be used once a week or something like that)

Any suggestions?

Thanks again

Alex

PROC Star
Posts: 1,236

Re: How to call a stored process within another stored process

Hi Alex,

There was a good discussion of options for submitting jobs in the background and kicking off separate batch sesssions in this thread:

https://communities.sas.com/thread/34901

--Q

New Contributor
Posts: 4

Re: How to call a stored process within another stored process

forgot to say, sas9.2, IE8

Respected Advisor
Posts: 3,124

Re: How to call a stored process within another stored process

If you are running SAS 9.3 or newer, then try this:

SAS(R) 9.3 Stored Processes: Developer's Guide

Haikuo

Trusted Advisor
Posts: 1,300

Re: How to call a stored process within another stored process

PROC STP and PROC DOCUMENT certainly streamline the process you are looking for, as long as you have 9.3+.  For older version of SAS you may want to look over the suite of macros' provided in the following paper:

SAS Stored Processes

Executing a Stored Process in SAS DMS/Batch by Interfacing with Metadata Server

Ian J. Ghent, Credit Risk Manager – Bell Canada, Ottawa, ON

http://www.sasopus.ca/docs/jun3_09/Executing%20a%20Stored%20Process%20in%20SAS%20DMS%20or%20Batch%20...

Valued Guide
Posts: 3,208

Re: How to call a stored process within another stored process

alexandraviera, what you are describing is:

- what is done in a RDBMS with locking according some acid requirements ACID - Wikipedia, the free encyclopedia "the transaction".
- the spread of work and the proceeding of tasks is workflowmanagement. Workflow management system - Wikipedia, the free encyclopedia


When you have SAS/Share you could use the locking facility as that is one of the parts making SAS table access RDBMS like. If you have already RDBMS review your access methods as they should give you the options to uses that. Having locking on records the should disappear by the server when the connection is closed.


Workflowmanagement is an other part. You can build that as you are doing, but keeping the correct status of that is your programming challenge.
I assume you will need a dedicated table or some of them for that. That is the design of your information flow.         

---->-- ja karman --<-----
Ask a Question
Discussion stats
  • 9 replies
  • 841 views
  • 1 like
  • 5 in conversation