topic Re: Stored Process is unavoidably returning dataset to EG [Library on server SASApp not found] in SAS Enterprise Guide

Stored Process is unavoidably returning dataset to EG [Library on server SASApp not found]

arthurcarj — Wed, 27 Jan 2021 13:23:54 GMT

Hi folks!

I started working with SAS Enterprise Guide about 5 months ago and it's been a fun learning experience. I am trying to build a Stored Process to facilitate the life of my teammates when running queries. I know that Stored Processes seem to be mostly associated with web applications and whatnot, but that's not what I'm looking for. I just want to leverage their interfacing capabilities and use those hierarchical cool prompts.

TOO LONG DIDN'T READ
My stored process returns a dataset to my process flow, but that dataset is defined on a libname unknown to EG. I want suppress that output, force the stored process not to return it. I don't want the PROMPT_DATA dataset appearing in the Process Flow.

The long version:

The problem I'm facing is related to the Stored Process output. However, let me describe the overall approach I'm taking.

The Stored Process Side

My stored process "Query Prompt" runs first; the user enters various fields (mostly texts and dates) and hit Run.
The stored process collects all those internal macro variables and builds a dataset called Prompt_Data.
Still within the Stored Process, I copy this dataset from WORK to a permanent location (UserHome), like shown in the snippet below.

/* Define home location for user */
libname UserHome base "/data/home/&sysuserid";

proc datasets nolist nodetails;
	copy in=WORK out=UserHome memtype=data;
  	select Prompt_Data;
run;

libname UserHome clear;

dm 'log;clear;output;clear;';

The EG Side

At this point, all user inputs are available as a table in the home folder, so I can access the results in my Enterprise Guide environment. I built a SAS program called "Importing Prompts into Macro Variables", which will basically move the table Prompt_Data from home to WORK (now the EG work though), and define a macro variable for each entry in that table.

This is the portion of my process flow that relates to those two stages:

My actual problem: as you can see above, my Stored Process is outputting the PROMPT_DATA table (its file name being USERHOME.PROMPT_DATA). The very first time I run the process, it all goes well. However, let's say that I save my project and restart EG. In this scenario, the PROMPT_DATA output icon will still show up in the process flow due to my previous run. Then, if I try to run the stored process again, I will get the following error:

USERHOME is defined within the stored process so it makes sense that EG won't know what it is, what I don't understand is why it's being returned to EG to being with. I can make it work if I manually delete PROMPT_DATA from my Process Flow, but that would be impractical. What I need is for my Stored Process to NOT output that in the first place! I've been looking for a solution to this for a while now and decided to give it a try with the community. I know my post is way too long, but I would rather over than underexplaining this.

Thank you in advance for any help, it will be much appreciated!

Arthur

Re: Stored Process is unavoidably returning dataset to EG [Library on server SASApp not found]

ChrisHemedinger — Wed, 27 Jan 2021 13:09:44 GMT

EG will attempt to add any data set that it detects was created when your code runs. If EG receives a "data created" event from the SAS session and the data exists when the code finishes, EG will gather these and add them as output data nodes in your flow.

A simple approach to avoid this, if possible, is to unassign the library at the end of the code item.

libname USERHOME clear;

Your data files remain, but EG won't find them to add into the flow.

Re: Stored Process is unavoidably returning dataset to EG [Library on server SASApp not found]

arthurcarj — Thu, 28 Jan 2021 20:36:05 GMT

Hi Chris, thank you very much for your reply! I forgot to include that in my question, but unfortunately that line is already in my code! I will edit my question to reflect that, sorry for if that was misleading.

Given the condition "the data exists when the code finishes", do you mean in the work library or anywhere? I could delete the data from the session work (or do a move instead of a copy), but it would still exist in the user home (external to the session).

Re: Stored Process is unavoidably returning dataset to EG [Library on server SASApp not found]

arthurcarj — Thu, 28 Jan 2021 20:34:19 GMT

Okay, I've found a workaround to this problem and thought of sharing here for closure and if someone goes through the same issue.

Since clearing the libname wasn't working, I decided not to use it altogether, and to replace any mention to UserHome for something else. Depending on what you're doing, I guess it might be hard to get rid of libname, but for my case it was doable. I found out that you can use actual paths in certain commands, so that's how I was able to solve this issue. After getting rid of using libname, the code became the following:

proc sql;
create table "/data/home/&sysuserid/Prompt_Data" as
    select 	trim(Prompt_Input) as Prompt_Input,
			trim(Macro_Variable) as Macro_Variable,
			trim(Value) as Macro_Value	
    from Prompts
;quit;

/* Print to report */
title "Table - Prompt variables";
proc print data="/data/home/&sysuserid/Prompt_Data"; run;