Recently, I was working with a client who had a unique problem. He needed a way to cancel a stored process from executing in cases where the stored process wasn’t registered to the matching Metadata folder-structure for its selected server context. For example, if a stored process was stored under the DEV folder-structure in Metadata but attempted to be run on the PROD server context, the execution of the stored process would then need to be canceled and alert the user of the error.

Fortunately, this type of behavior with stored processes can be achieved by injecting SAS code that runs before the stored process executes via Request Initialization of the Logical Stored Process Server.

Preliminary requirements

A stored process registered on one logical environment (e.g., DEV) won’t run on the server context of another logical environment (e.g., PROD).

The following steps show you how to set up a Request Initialization for a Logical Stored Process Server in order to cancel a stored process from executing on the PROD server, if it isn’t stored in PROD in metadata.

1) Write the desired injection code. In this case, the code checks if the stored process is registered with PROD and then either cancels the stored process or proceeds with normal execution.

/* This code checks macros variables populated by the currently executing stored process to discover the path in metadata where the currently executing stored processes is located. It then compares this metadata path with the expected path for the server context this code will be injected into. For example, the redacted line below might say “PROD” to signify the top-level. It cancels if the path is not a match; otherwise, execution continues normally. */
%macro CheckProdStpRegistration();
	%let stp_path =;
	%if %symexist(_METAFOLDER) %then %let stp_path = &_METAFOLDER;
	%else %if %symexist(_PROGRAM) %then %let stp_path = &_PROGRAM;
	%else %do;
		%let stp_path = INVALID_REGISTRATION;
		%put ERROR: The macro variables _METAFOLDER and _PROGRAM are not 
      defined. Cannot determine stored process registration.;
	%end;
 
	/* Check top-level filepath is what is expected in metadata PROD folders. */
%if ^(%scan(&stp_path,1,%str(/)) = Logical
AND %scan(&stp_path,2,%str(/)) = PROD) %then %do;
		%put ERROR: Only stored processes registered in PROD can be run on an 
      PROD-based Stored Process Server. Canceling the stored
      process execution.;
 
		/* Cancel the stored process from running. */
		data _null_;
			abort cancel;
		run;
	%end;
%mend CheckProdStpRegistration;
 
%CheckProdStpRegistration();

In this example, the code checks the macro variables that are populated with running a stored process. After retrieving the metadata path where the stored process is stored, the code then checks that the first and second parts of the path are equivalent to “Logical/PROD”. This matches the logical PROD environment from above.

2) Now that we have our code, we can set it to run before every executed stored processes via a Request Intialization under the Logical Stored Process Server options.

NOTE: After changing this property, you’ll have to refresh the Object Spawner so that the changes take hold.

3) Test a stored process located metadata under the DEV folder structure and test a stored process in metadata located under the PROD folder structure. Ensure the stored process is set to execute against the Application server that the Request Initialization code was applied to (e.g., the PROD server context).

You should not that the part of the code that is essential to canceling the execution of a stored process is the “abort” statement with the “cancel” option. There is no other way to gracefully terminate a stored process’s execution. Other methods will either allow the stored process to still run or cause errors that can affect the current session and mislead the end-user.

/* Cancel the stored process from running. */
		data _null_;
			abort cancel;
		run;

In this example, the ‘abort cancel’ statement/option is shown. It simply just needs to run in a ‘null’ data-step in order to cancel the upcoming stored process execution.

I hope you found this tip helpful. Feel free to leave a question or comment in the space below.