Desktop productivity for business analysts and programmers

Getting a stored process to run in my current session

Accepted Solution Solved
Reply
Contributor
Posts: 45
Accepted Solution

Getting a stored process to run in my current session

Hi,

I have a problem using stored processes in enterprise guide. (a specific problem rathen than my general dislike for them but nevermind)

I have an enterprise guide project that will be used by many other users which contains 9 separate process flows, most of the flows are structured so that the user runs the first program to do some data manipulation steps that output to work. the user then runs some reports/comparisons on the work table to decide if the changes are acceptable and then runs another program to save the new dataset.

I also have an autoexec flow that defines a whole set of macros and macro variable that are used elsewhere in the project, some of which that is driven from prompts in the autoexec itself.

My problem has arisen because i need one of my programs in a later process flow to have cascading prompts and so i must resort to converting the program into a stored process.

Creating the stored process and the prompts i require was fine, I even managed to successfully get the prompts to dynamically update depending on choices that were made in the autoexec prompt. However when the stored process runs i get total failure in that it cannot find any of my macros or macro variables that were defined in the autoexec which i think is caused because the stored process always runs in it's own session.

Does anyone know how i might be able to get the stored process to run in a way that it has full access to my current enterprise guide session?

Tim


Accepted Solutions
Solution
‎12-10-2012 09:30 AM
Contributor
Posts: 45

Re: Getting a stored process to run in my current session

Thanks Nick and Tom,

I have finally got it working, although now seem to be unable to flag this up as answered???

For future reference and for anyone who hits the same problem here is what I did to get a cascading prompt in enterprise guide that relies on macros elsewhere in the project and returns data to the current session...

I took all of the code from my enterprise guide autoexec flow that did not rely on prompt values and saved it onto my server.

I then replaced the code with a new program that had a %include to the saved code to test that it all worked fine.

I created new macros that create a data set (if it doesn't already exist) in the user's Home directory on the server and then save the value of a macro variable into the table with an ID column that I can use later to identify the project in case I need the same trick elsewhere. Code for these macros at the end...

Then in the autoexec code in my project I use these macros to save any macro variables I want to be available to a stored process.

The most important one being %Save_MV(MyWork, %sysfunc(getoption(work)), ProjectID); which saves the full path of the current work directory.

Then before converting my standard EG program into a stored process I add some lines at the start to %include all the same code as in the autoexec and use %Restore_MV to re-define any macro variables I need for the process. (Remembering to use %global to define them in the global symbol table first!).

I then take the value of &MyWork to create a libname statement that points to the work area for my user session and update the code to output any final tables to this directory.

I created the stored process to run on the WORKSPACE server rather than a stored process server, this means that the new session that the stored process uses is created with the same credentials as the user who triggered it so I have no permissions issues connecting to the home area or writing to the other session's work area.

I can then create the dynamic cascading prompts that I want and have the output returned to the work area of my current session.

some example code:

/* Program 1 - this defines the macros i need and tests saving a macro variable */

%macro Save_MV(MVName, MVValue, MVSysID);

      libname MyHome base "/home/&SYSUSERID";

      %if not %sysfunc(exist(MyHome.SPMacro,DATA)) %then %do;

           data MyHome.SPMacro;

                Length SysID $32 Name $32 Value $200;

           run;

      %end;

      data tmp_Record;

           Length SysID $32 Name $32 Value $200;

           SysID="&MVSysID";

           Name="&MVName";

           Value="&MVValue";

      run;

      proc sql noprint;

           delete from MyHome.SPMacro where strip(name)="&MVName" and strip(SysID)="&MVSysID";

      quit; 

      proc append base=MyHome.SPMacro data=tmp_Record;

      run;

      proc datasets lib=work memtype=data force nolist nowarn;

           delete tmp_Record;

      run;

      Libname MyHome clear;

%mend;

%macro Restore_MV(MVName, MVSysID);

     libname MyHome base "/home/&SYSUSERID";

     %if not %sysfunc(exist(MyHome.SPMacro,DATA)) %then %do;

           %Put ERROR-No Macro variables have been saved for the current user;

      %end; 

     proc sql noprint;

           select strip(value) into :&MVName from MyHome.SPMacro where strip(name)="&MVName" and strip(SysID)="&MVSysID";

     quit;

     Libname MyHome clear;

%mend;

%Save_MV(TestMV, TESTING, MyProject);

%Save_MV(MyWork, %sysfunc(getoption(work)), MyProject);

/* Program 2 - this gets turned into a stored process */

%global TestMV MyWork;

%Restore_MV(TestMV, MyProject);

%Restore_MV(MyWork, MyProject);

libname MyWork base "&MyWork";

data mywork.TestOutput;

     MV_Value="&TestMV";

run;

Libname MyWork clear;

View solution in original post


All Replies
Super User
Posts: 3,233

Re: Getting a stored process to run in my current session

While I've not really done much with stored processes, from what I know of SAS's BI server architecture, EG processes run on a Workspace Server under the user account supplied in your EG connection profile. Stored Processes on the other hand run on a Stored Process server that runs under a SAS service account. So your comment that a stored process runs in it's own session is correct. How about you %include your autoexec into your stored process to get those macros and macro variables defined?

Contributor
Posts: 45

Re: Getting a stored process to run in my current session

I had thought about that, however;

The autoexec flow also has prompts in it which I would need for the process and don't want to have to prompt for again.

I could save to prompt values into a temporary table on the server that the stored process could query however there is a possibility that multiple users will need to run the project at the same time (and possibly the same user miltiple times at the same time). So I would need some way to identify the user and the session they are running in the temporaray table but then I am back to my original problem in that I would need to get a macro variable value from the user session into the stored processes.

I had also thought about using proc stp to pass all of the prompt values into the stored process but that is fairly pointless as the only reason I want a stored process is the first place is for the cascading prompts that I can't have in a normal EG program.

Also the program that I want to turn into a stored process runs in a way that creates a work table for the user to perform checks on before they save into a permenant table. This causes more problems because, with the stored process running in another session it wipes out the work table that i took half an hour to create when it finishes.

The tables created can be up to 100 Million records and take up over 60Gb of space so I would rather keep them in the work area until I know I need to use up permenant storage.

Tim

PROC Star
Posts: 1,143

Re: Getting a stored process to run in my current session

I'm not sure you're going to find a solution you like. Your requirement for and use of WORK tables is quite clear and sensible, but I simply don't see how you can retain that between different stored processes. It doesn't seem to be part of the architecture. All I can think of is to create a permanent directory on your server, and then for each user:

1. In the autoexec, create a subdirectory for them;

2. Link that to a SAS library name, and use it throughout your user's session;

3. As part of your termination code, delete the temp files and remove the directory.

Actually, it doesn't sound that bad. Also, this would be an ideal place to store and load the data for your global macro variables.

That's all I can think of,

  Tom

Frequent Contributor
Posts: 91

Re: Getting a stored process to run in my current session

Hi Tim,

I feel for you with this one. However, one solution that may help is to pass the current worspace path of your SAS Entperprise Guide session to the stored process so that it writes to this rather than the workspace it creates itself. This then makes the datasets available in the EG workspace for subsequent tasks. You can either use proc STP, or, if you require prompts, as a prompt value which references the sashelp table that containes the current workspace path.

If you think this can help, I can write more about it for you.

Good luck.

Nick

Solution
‎12-10-2012 09:30 AM
Contributor
Posts: 45

Re: Getting a stored process to run in my current session

Thanks Nick and Tom,

I have finally got it working, although now seem to be unable to flag this up as answered???

For future reference and for anyone who hits the same problem here is what I did to get a cascading prompt in enterprise guide that relies on macros elsewhere in the project and returns data to the current session...

I took all of the code from my enterprise guide autoexec flow that did not rely on prompt values and saved it onto my server.

I then replaced the code with a new program that had a %include to the saved code to test that it all worked fine.

I created new macros that create a data set (if it doesn't already exist) in the user's Home directory on the server and then save the value of a macro variable into the table with an ID column that I can use later to identify the project in case I need the same trick elsewhere. Code for these macros at the end...

Then in the autoexec code in my project I use these macros to save any macro variables I want to be available to a stored process.

The most important one being %Save_MV(MyWork, %sysfunc(getoption(work)), ProjectID); which saves the full path of the current work directory.

Then before converting my standard EG program into a stored process I add some lines at the start to %include all the same code as in the autoexec and use %Restore_MV to re-define any macro variables I need for the process. (Remembering to use %global to define them in the global symbol table first!).

I then take the value of &MyWork to create a libname statement that points to the work area for my user session and update the code to output any final tables to this directory.

I created the stored process to run on the WORKSPACE server rather than a stored process server, this means that the new session that the stored process uses is created with the same credentials as the user who triggered it so I have no permissions issues connecting to the home area or writing to the other session's work area.

I can then create the dynamic cascading prompts that I want and have the output returned to the work area of my current session.

some example code:

/* Program 1 - this defines the macros i need and tests saving a macro variable */

%macro Save_MV(MVName, MVValue, MVSysID);

      libname MyHome base "/home/&SYSUSERID";

      %if not %sysfunc(exist(MyHome.SPMacro,DATA)) %then %do;

           data MyHome.SPMacro;

                Length SysID $32 Name $32 Value $200;

           run;

      %end;

      data tmp_Record;

           Length SysID $32 Name $32 Value $200;

           SysID="&MVSysID";

           Name="&MVName";

           Value="&MVValue";

      run;

      proc sql noprint;

           delete from MyHome.SPMacro where strip(name)="&MVName" and strip(SysID)="&MVSysID";

      quit; 

      proc append base=MyHome.SPMacro data=tmp_Record;

      run;

      proc datasets lib=work memtype=data force nolist nowarn;

           delete tmp_Record;

      run;

      Libname MyHome clear;

%mend;

%macro Restore_MV(MVName, MVSysID);

     libname MyHome base "/home/&SYSUSERID";

     %if not %sysfunc(exist(MyHome.SPMacro,DATA)) %then %do;

           %Put ERROR-No Macro variables have been saved for the current user;

      %end; 

     proc sql noprint;

           select strip(value) into :&MVName from MyHome.SPMacro where strip(name)="&MVName" and strip(SysID)="&MVSysID";

     quit;

     Libname MyHome clear;

%mend;

%Save_MV(TestMV, TESTING, MyProject);

%Save_MV(MyWork, %sysfunc(getoption(work)), MyProject);

/* Program 2 - this gets turned into a stored process */

%global TestMV MyWork;

%Restore_MV(TestMV, MyProject);

%Restore_MV(MyWork, MyProject);

libname MyWork base "&MyWork";

data mywork.TestOutput;

     MV_Value="&TestMV";

run;

Libname MyWork clear;

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 1115 views
  • 6 likes
  • 4 in conversation