SAS GRID Getting a table in the work environment of a user with a Stored Process

[VtotheG]

Hi All, 

We are using SAP HANA in combination with SAS GRID (Enterprise Guide). In SAP HANA we have defined several analytical tables which use input parameters (placeholders) to get data (so that we can access historical views of the data). 

Example: 

/*
proc sql;
connect using hdp;
create table work.output as
select 
*
from    
connection  to hdp (
select 
Customer,
Age
from 
     "SAP_TABLE"
('PLACEHOLDER' = ('$$DATE$$',
	 '20181201'))  	
) z
;
disconnect from hdp;
quit;

Most of the users in my organisation are not comfortable using SAS Program code. That is why i wanted to create a stored process with a input field (so that it is still a point and click solution for my users).  The problem is that a stored process doesn't run in in the same session as the user interface. This means that the work table ends up in a different work and is not accessible to my users.

I am aware that i could create an additional library, but i don't want the data to persist multiple times in our sas environment (to avoid duplicate data or accidental overwrites by different users). 

I have tried the solution to pass the absolute work location (path on the filesystem) to the sas stored process using. 

"%sysfunc(pathname(work))",

This works well if the SAS GRID stays on the same hostname, but because the code can run on different hostnames (due to the SASGRID setup) it is still possible that the absolute path is not accessible. 

I have also set the stored process to workspace server only. 

Does anybody have a good idea how i could implement such as setup (or do something that is a bit more straight forward)?

environment: SAS GRID. Client: SAS Enterprise Guide 7.1

[tomrvincent]

No problem. I do this all the time. Create a job and userid specific work folder so that it is unique.

Something like this:
libname EXT "&OutputPath.&YMD8/&SYSUSERID/&SYSJOBID/Extract";

[VtotheG]

Do how do you remove / clean the folder after the user closes the session? Do you have a daily script?

[tomrvincent]

You can just delete it at the end of the job if you want. I don't because we use them for auditing and validation.