Returning a SAS Dataset

[deleted_user]

The definition of a Transient Package states that:

Transient package output returns a temporary package to the client. The package can contain multiple entries, including SAS data sets, HTML files, image files, or any other text or binary files. The package exists only as long as the client is connected to the server.

We're trying to run a basic Stored Process that will query a database and return a SAS dataset to the user. We are currently writing out these datasets to a permanent location but are considering a change in the functionality that will return the dataset to the Enterprise Guide session used to run the stored process and make it available only for the duration of that session.

I can get HTML results but we really only want the SAS dataset that is generated by the stored process. I can see from the log that the SAS dataset (in the work library) is being generated but where is it going? Should the code be writing to a different library?

Has anyone tried this?

[deleted_user]

Hello,

Does anyone have any thoughts on the question posted above?

Thanks...

[Andreas]

Documentation states that files can be written to the folder where macro variable &_stpwork points to and that %stpend will collect all the contents of the folder to a package.

I tried it with the following code in a stored process but it does not work in Enterprise Guide. The Dataset is written to the result package but Enterprise Guide does not display it correctly. I think we cannot use StPs to return SAS Datasets to SAS Enterprise Guide.

%LET _ODSDEST=NONE;

%STPBEGIN;

LIBNAME pkg "&_STPWORK";
PROC DATASETS LIB=sashelp NOLIST;
COPY OUT=pkg;
SELECT prdsale;
QUIT;

%STPEND;

[deleted_user]

You can use the pathname function to access the temporary work library that gets created during your session. The following code works and returns a dataset to your EG session when run as a stored process:

%global path;

%macro FindWorkPath(inLibref=WORK);
data _null_;
length path $256;
path = pathname("&inLibref");
call symput("path", path);
run;
%mend;

%FindWorkPath;

%put &path;

libname worktmp "&path";

data worktmp.test;
x=1;
y=2;
z=5;
run;

(Thanks Peter and Vince).

[deleted_user]

Further to my last post - your Results setting should be set to Transient Package.

[Andreas]

I could not get this to work in my environment. The code you provided generates an empty HTML result page in Enterprise Guide when executed as a stored process. The SAS dataset is created by the stored process, as I can see in the SAS log, but it is not accessible from the SAS session behind Enterprise Guide. The stored process does not use the same workspace session, even if i direct it to use a workspace server instead of a stored process server. Why do you use the path function instead of writing directly to the work library?

[Vince_SAS]

Currently, Enterprise Guide does not support inserting a link to SAS tables that were returned via a result package. However, if you are willing to incur a few extra steps, there may be a workaround.

Register this code as a stored process that creates a transient result package:

*ProcessBody;



*  Force to HTML output;



%let _ODSDEST=html;



%STPBEGIN;

  *  Create the output table in the "magic" directory;

  libname stpwork "&_STPWORK";

  data stpwork.test; 

    x=1; y=2; z=3;

  run;



  *  Close the HTML destination and delete the HTML file;

  ods &_ODSDEST close;

  data _null_;

    length fileref $8 rc 8;

    rc = filename(fileref, "&_STPWORK.main.html");

    rc = fdelete(fileref);

  run;

%STPEND;

When you run the stored process from EG, you will be presented with a "file download" dialog. Choose to save the file in a directory of your choosing. You can specify any name for the file, but make sure that the file extension is "sas7bdat". You can then drag-and-drop the file into EG, or use the EG menu sequence
File > Open > Data...
to open the table. You can delete the file when you are finished with it.

Vince
SAS R&D