STP output to work area in EG session

[Clash]

Hi!

Is there a solution to where I output my data from a generated STP to a temporary area, reached within my EG session from where I execute my STP? I have a situation where I generate a lot of tables to a permanent library but depending who is running the STP, the tables are different. As for now, I've created a suffix to each table (&sysuserid.) as a guide which tables that were generated by certain user (before the tables are saved, all existing tables with user suffix is deleted).

There would be much nicer if the results from the STP could be accessed by a temporary library OR, in some way, outputted to my work area in the EG session. Could an approach like this work? (&SASWORKLOCATION is from EG session) 

Data QC_DeleteMe ;

          Set sashelp.class ;

Run ;

%let tmpHost = "MY  SERVER" 7554 ;

signon tmpHost AUTHDOMAIN="auth" ;

          %syslput SASWORKLOCATION=&SASWORKLOCATION. ;

          rsubmit ;

                   libname _QC "&SASWORKLOCATION." ;

              Proc Download

                             Data=work.QC_DeleteMe

                             Out=_QC.QC_DeleteMe ;

                   Run ;    

          endrsubmit ;

signoff ;

Is there any "neat" way to solve this?

Best regards,

Claes

[Kurt_Bremser]

Hmm. That sounds interesting.

Maybe @ChrisHemedinger could create a custom task that retrieves the value of a macro variable from a workspace server session and sets it as a prompt value for a task that runs on another environment (ie a STP).

But keep in mind that WORK locations are writable (even accessible) only by the owning user; since STPs often run on a pooled server with user sassrv, they would not be able to write into your WORK. Only STP's configured to run on a workspace server would be able to do this, IMO.

[BrunoMueller]

Hi

Have a look at Proc STP this allows you to call a Stored Process, run the code and return the data to the calling environment.

Doc is available here http://support.sas.com/documentation/cdl/en/stpug/68399/HTML/default/viewer.htm#p0p6ncj6i4s8w5n1a8jx...

Please note:

• the Stored Process should not use the %stpbegin and %stpend macro if you are not creating any output in your stp, but rather just tables.
• The code is not actually run on the Stored Process Server, but rather the same process that runs your code
• the name given in the OUTPUTDATA statement like   outputdata  sugus_mvar=work.stp_mvars; is avialable within the Stored Process code as macro variable _TARGET_SUGUS_MVAR

Here is the code of the Stored Process:

%let carMake = %lowcase(&carMake);
data &_TARGET_myCars;
  set sashelp.cars;
  where lowcase(make) = "&carMake";
run;

proc sql;
  create table &_target_sugus_mvar as
  select
    *
  from
    dictionary.macros
  order by
    scope
    , name
    , offset
  ;
quit;

Here the code using Proc STP

proc stp
  program='/someFolder/STP/createData'
;
  list;
  inputparam carMake="Audi";
  outputdata  myCars=work.stp_data;
  outputdata  sugus_mvar=work.stp_mvars;
run;

Bruno

[Kurt_Bremser]

There's always something to learn. Or be reminded of. Great answer, @BrunoMueller!

[Clash]

Hi!

Thanks for your quick replies. Tried it but....I can't make it work -->

NOTE: PROC_STP: ====== Stored Process: /BI Content/DeleteMe ======
NOTE: PROC_STP: ====== Metadata Listing for /BI Content/DeleteMe ======
NOTE: Stored Process Name: DeleteMe
NOTE: Description:
NOTE: Creation Date: 08Feb2017:13:56:08
NOTE: Modification Date: 13Feb2017:10:20:32
NOTE: Keywords:
NOTE: Responsible Parties: MyUser
NOTE: Server Context: SASApp
NOTE: Stored Process code may run on any available server
NOTE: Execution required to be on SASApp application server only
NOTE: Source Code Repository: Source code stored in metadata
NOTE: Result Type: Packages Yes
NOTE: Streaming Yes
NOTE: Output Parameters:
NOTE: OutputData Sources available:
NOTE: Target: Data Table Table Parameter: _TARGET_myCars
NOTE: Label: _TARGET_myCars
NOTE: Target: Data Table Table Parameter: _target_sugus_mvar
NOTE: Label: _target_sugus_mvar
NOTE: Input Parameters: CARMAKE = Audi
NOTE: _result = STREAM
NOTE: _metaperson = 
NOTE: _metauser = 
NOTE: _client = PROCSTP; TKESTP; JVM 1.6.0_21; SunOS (sparc) 5.10
2 The SAS System 11:06 Monday, February 13, 2017

NOTE: PROC_STP: ====== End Metadata Listing for /BI Content/DeleteMe ======
NOTE: PROC_STP: ====== Stored Process: /BI Content/DeleteMe Return Status = 0 ======
NOTE: PROCEDURE STP used (Total process time):
real time 0.16 seconds
cpu time 0.03 seconds

I'm not using the %beginstp but what about my settings "Execution Options" and "Data Sources and Targets"? Tried some different settings but none seems to get my output data in work area.... (please view the attached two files)

(I'm using EG 7.12)

Regards,

/Claes

[BrunoMueller · Accepted solution]

In my example I did not specify any data sources in the Metadata of the stored process.

Did you have a look at the datasets you specified in the OUTPUTDATA statement where they created, what is the content?

Which SAS Version are you using? The Version of EG should not matter.

Bruno

[Clash]

Hi Bruno!

Sorry for the slight delayed answer, had to sit down in a quite room before I figured what was wrong.

Big thanks for your help, really helped me!

Best regards,

Claes