Can't use temporary work table generated by transformation in CODE EDITOR SAS DIS 4.904

[ErikLund_Jensen]

Hi @songsujing 

When a job is run, a new sas instance is created to run the job, and a new directory is created and allocated to the session. When the job is closed, the work directory is deleted. Subsequent runs in the open job use the same sas instance, so transformations can be run one by one.

When something is run in the code editor, then same happens, a new sas instance is created to run the code, and a new directory is created and allocated to the session as work library. So the work library in the code editor does not refer the same physical directory as the work library in the open DI Studio job.

You can fix it in the following way:

In the DI Studio job, load the work table to a permanent table in a test library.

in the code editor: allocate the same library with a libname.

[LinusH]

Another option could be to paste the existing transformation code into the code editor (or EG/Studio) and submit it.

[ErikLund_Jensen]

@LinusH 

Sorry, but it seems that you moved this post without the original question, which is left without answers in "Administration and Deployment" and missing here. 

Hi @songsujin 

I like the suggestion from LinusH, because all the trouble with an exira loader and a permanent table could be avoided, but I see several problems:

In the Code Editor:

• The job could be long-running, so you would have to wait a long time before the relevant work table is recreated.
• The job code generated by DI Studio and the resulting log is BIG and not what you would like to work with in the code editor.

In Enterprise Guide:

• EG runs in another Server Context with it's own config, so the code could fail because of missing environment variables or missing acces to input data.

So I got another idea that is much simpler, because it makes the Job Work Library accessible from the code editor, which is what you wanted. The following description might look complicated too, but it was not when I tried it. 

First thing: Make sure that the work table is created as a table and not as a view (right click on it and uncheck "Create as Wiew").

Then add the following as post-code to the transformation that generates the work table. You could just leave it there because it does no harm, unless you have a nosy administrator with a negative view on pre- and post code (they exist, I am one myself).

%put %sysfunc(getoption(work));

Run the transformation. The path/name of the current work library is written to the log:

792        %put %sysfunc(getoption(work));
/saswork/udvk/di/SAS_workC4100000448F_sasc03.odknet.dk/SAS_workB2630000448F_sasc03.odknet.dk

You now have the prequisites in place to ude the code editor.

Then copy the path/name and paste it into the code editor. 

Change it to a libname (don't use the libref work) and add your code in the code editor. You will need the table name, which you could copy from the Basic Properties pane.

libname jobwork "/saswork/udvk/di/SAS_workC4100000448F_sasc03.odknet.dk/SAS_workB2630000448F_sasc03.odknet.dk";

data _null_;
	set jobwork.WQVGYK (obs=1);
	put _all_;
run;

6                                                          The SAS System                          10:25 Saturday, February 12, 2022

26         libname jobwork "/saswork/udvk/di/SAS_workC4100000448F_sasc03.odknet.dk/SAS_workB2630000448F_sasc03.odknet.dk";
NOTE: Libref JOBWORK was successfully assigned as follows: 
      Engine:        V9 
      Physical Name: /saswork/udvk/di/SAS_workC4100000448F_sasc03.odknet.dk/SAS_workB2630000448F_sasc03.odknet.dk
27         
28         data _null_;
29         	set jobwork.WQVGYK (obs=1);
30         	put _all_;
31         run;

Metaserver=sasmetaprod.odknet.dk FlowID=A5NTEQFN.BR00000I FlowNavn=Medarbejderliste_ITD_XP FlowDescription=  FlowEndBehaviour= 
DatoTid_Flow_Oprettet=08/08/2018 15.15.49 DatoTid_Flow_Opdateret=18/10/2018 10.39.46 FlowFolderID=A5NTEQFN.AA0001PW _ERROR_=0 _N_=1
NOTE: There were 1 observations read from the data set JOBWORK.WQVGYK.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

P.S.

If this is something thay you or others would use frequenty, you could persuade your administrator to add the post code line to the Server Context autoexec_usermods. Then the current work directory would be put out in the top of every job log.

[Tom]

I don't know anything about SAS/DI but if the goal is just to define a second libref that points to the same place as WORK just use the %SYSFUNC() call in the LIBNAME statement directly.

libname jobwork "%sysfunc(getoption(work))";

Or even easier skip the macro code and just use base SAS syntax instead.

libname jobwork (work);

[ErikLund_Jensen]

I'm sorry, but that wouldn't work in this case. The whole idea is to give ONE SAS session access to ANOTHER concurrent session's work library, so the libname must be hardcoded, 

The problem in this post is not a DI Studio priobem. DI Studio is a code generator that sends code to execution, and each open window in DI Studio will have it's own workspace server session. So it is just the same as having two Display Manager sessions open at the same time and finding a way to get access from one session to the other session's work library. 

[songsujing]

I created a job in SAS DIS 4.904 and run the selected transformation to generate a temporary table, I would like to use this temporary table to develop the code for next transformation in CODE EDITTOR but this table is not available, I can use table inside the job. can't figure out why?  ( temporary work table)

Thanks a lot