topic Re: Can I run a stored procedure in my current workspace session in EG? in SAS Enterprise Guide

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

RobWanders1 — Fri, 06 Oct 2023 09:22:18 GMT

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

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).
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?

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

LinusH — Fri, 06 Oct 2023 10:37:16 GMT

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?

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

RobWanders1 — Fri, 06 Oct 2023 11:10:55 GMT

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.

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

LinusH — Mon, 09 Oct 2023 07:39:50 GMT

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

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

RobWanders1 — Mon, 09 Oct 2023 08:57:54 GMT

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.

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

Patrick — Mon, 09 Oct 2023 09:09:39 GMT

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).

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

LinusH — Mon, 09 Oct 2023 12:14:16 GMT

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.

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

AlanC — Mon, 09 Oct 2023 12:27:54 GMT

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.

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

RobWanders1 — Wed, 11 Oct 2023 08:46:50 GMT

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?

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

yabwon — Wed, 11 Oct 2023 09:01:12 GMT

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