Can I run a stored procedure in my current workspace session in EG?

[RobWanders1]

When I use the query builder in SAS EG to write a file to CAS, the table becomes a session-scope in-memory table. When I want to promote this table to a global-scope table and save this table to disk aswell, I run the following code.

data _null_;
	call symputx('libref',scan("&syslast",1,'.'));
	call symputx('tablename',scan("&syslast",2,'.'));
run;

%let caslibname = %sysfunc( libref_to_caslibname(&libref) );    /* libref_to_caslibname is a function I wrote to retrieve the caslibname from the libref  */

proc casutil incaslib="&caslibname" outcaslib="&caslibname";
	promote casdata="&tablename";
quit;

proc casutil incaslib="&caslibname" outcaslib="&caslibname";
	save casdata="&tablename";
quit;

This works great, but I want other users to easily acces this code snippet aswell. I tried making a stored procedure for this code, but the problem is that the stored procedure executes in a new workspace session. This means that

1. My &syslast doesn't contain the table I want to promote and save. I tried using a prompt and use &syslast as an input for the promt but this doesn't work (just the string '&syslast' is passed, instead of the value of &syslast). 
2. With a new workspace session come a new cas session, so I couldn't even promote and save the tables if I solve problem 1.

Is it possible to run a stored procedure in the current workspace session? Or is there an alternative way to share this code snippet/program with end users? 

[LinusH]

I don't think that I can answer your specific question.

But, Stored Process is a discontinued technology, and you are already working with Viya...

Why don't create a macro instead?

[RobWanders1]

We are working with Viya for Visual Analytics only at the moment. Users are still working in EG, just the input tables for the dashboards are going to CAS. The reason I am not using a macro is because I want to keep it as simple as possible for the end-user. A stored procedure can be dragged into a flow and be attached to the output table of the query builder. Having them add a program step to the flow and manually insert the macro call might already be to much of an ask for some of them. Maybe I'm pampering them too much, if what I want is not possible I will probably make a macro instead like you say.

I also tried to share the program-step with other users but I couldn't find a way to do so, otherwise I would have done that instead of using a stored procedure.

[LinusH]

Another option could be to create a custom task.

It requires a small configuration of the clients though, so they can reach it/having it published to.

https://documentation.sas.com/doc/en/egdoccdc/8.2/egug/n0qrmwv1ttfkz0n1loj1tjt6t5hc.htm

[RobWanders1]

Thanks, this looks promising. I do have administrator privileges so I should be able to do the configuration, but from the documentation you linked it isn't quite clear to me how I would generate the DLL file. Both methods in the documentation require that there is already a DLL file for the task.

[Patrick]

May-be this blog will be helpful: Stored processes in Viya

Please note that this is for SAS Studio.

Using SAS EG will depend on your Viya version. At least for Viya 4 SAS EG is in the moment not supported (on the roadmap for sometime in 2024 as far as I know).

[LinusH]

Well, it seems that you need to develop some kind of .net package for this, so this might not be worth the effort.

Some resources:

https://support.sas.com/documentation/onlinedoc/guide/customtasks/index.htm

https://github.com/cjdinger/SasDataExample

I don't have EG at my fingertips so I can't help you find a work-around for you parameter/prompt problem. But it could be worth trying contacting SAS tech support to get an indication whether this is possible or not.

[AlanC · Accepted solution]

I have made numerous custom tasks in C# for EG. It isn't too bad. They can be used by other EG users and appear in the available tasks. They can be full-blown applications with numerous Windows, etc. 

EG has a few interfaces that have to be used. Visual Studio auto-implements them and you fill in what needs to be done. If you haven't worked with interfaces, see this  to understand them a bit. It will help when you read Chris' book:

(1) Interfaces in C# - What they are, how to use them, and why they are so powerful. - YouTube

Basically, they are a contract. It allows EG to call your custom task. For example, a car may have a IVehicle interface. All vehicles must explain how to accelerate. In EG, you will have contracts like 'RunTask' that tells EG what to run when the task opens. 

[RobWanders1]

I will give it a try, thanks. Just one more question: when I look at the documentation here https://support.sas.com/documentation/onlinedoc/guide/customtasks/index.htm it seems to be somewhat outdated, with examples that are compatible with EG4.1 & later. Should these examples work just fine for EG8?

[yabwon]

I say you could try one more option.

You could wrap it up in a macro in a package and make it available on the machine to other users.

Info in packages: https://github.com/yabwon/SAS_PACKAGES

Step by step "Hello World" example: https://github.com/yabwon/SAS_PACKAGES/blob/main/SPF/Documentation/HelloWorldPackage.md

Bart