BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
arthurcarj
Fluorite | Level 6

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

  1. My stored process "Query Prompt" runs first; the user enters various fields (mostly texts and dates) and hit Run.
  2. The stored process collects all those internal macro variables and builds a dataset called Prompt_Data.
  3. 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:

SAS.png

 

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:

SAS_error.png

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
arthurcarj
Fluorite | Level 6

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;

View solution in original post

3 REPLIES 3
ChrisHemedinger
Community Manager

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.

SAS Hackathon registration is open! Build your skills. Make connections. Enjoy creative freedom. Maybe change the world.
arthurcarj
Fluorite | Level 6

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

arthurcarj
Fluorite | Level 6

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;

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 769 views
  • 1 like
  • 2 in conversation