How temperory output tables are dropped when session is closed SAS DI

[Hercules]

Hello all,

How come temperory output tables are dropped/deleted which are generated after every transformation in SAS DI job. I mean is there a code written which automatically drops the table after the session is closed.

Was just curious about the architecture or sas logic behind it. How does it work?

[Hercules]

?

[LinusH]

If there is code you can check for yourself...

And basically all temporary output data is stored in saswork which is by default scratched at the end of the SAS session.

[Hercules]

Hello Linus,

No there is no code running in a job which specify like delete work library or dataset=library kill; or something else.

And yes by default if the job ended successfully then it will delete that work library, if aborted manually then it will not.

[mohamed_zaki]

Why do not you specify what you need exactly? Regard less how it is done in SAS DI studio.

Maybe if you rephrase your question in better way, we can help?

For example do you need to be able to delete all the data set in specific library without ending the session?

[Hercules]

My question is very simple. How the work library which is created for our session gets deleted?

Also if I terminate the job in between, why this work library folder resides on /temp and not get deleted.

[Hercules]

I have written a shell script and scheduled through cron jobs which will run at midnight to clean up the all the work tables which is 1 day old.

So deleting them is not an issue.

[mohamed_zaki]

If that what you're into it

then check this old thread

you can drop, delete all the table by the same way

[Reeza · Accepted solution]

I believe it's a setting, that defaults to clearing on closure of the session. You can change the behaviour in Base SAS not sure about SAS DI.

SAS(R) 9.3 System Options: Reference, Second Edition

[Hercules]

Thank you so much Reeza.

This was the answer I was looking for.

I will read more about workterm now.

[jakarman]

Anant is a little bit impatient to learning SAS and he is using DI. Coming form BO SAP/BW  there can be some concepts not being correct.

DI is a very nice tool of SAS for generating SAS code in a nice DTAP approach. As it is generating SAS code the SAS coding quirks are sometimes hidden or badly recognized.

The DTAP approach only when the technical architecture is done correctly to business requirements. These are commonly not aligned with some SAS ideas.  

With DI the explanation of work-tables is nicely documented at: SAS(R) Data Integration Studio 4.9: User's Guide

The transformations are needing to refer to tables as input/output. Many of those are just intermediate ones and uses on saswork.

You could use table Views in that case there will never be a table created.

The saswork is normally always cleaned as part of closing a SAS session. Problems always arise running out of space in the saswork. It makes sense DI will delete a temporary table in the saswork after it is nit necessary anymore. There are a lot of options to be set and I cannot find those in the online docs 

My eye did a hit on logging. not the questions here, but interesting: SAS(R) 9.4 Intelligence Platform: Desktop Application Administration Guide, Fourth Edition (Redirecting Output and Logging Information to a File) It is an other part of the DI configuration.

A lot of ETl is found at http://support.sas.com/resources/papers/ETLperformance07.pdf page 4 tells exactly what is done on temp tables.

Threre is no explanation why the people building the DI tool made this choice. Only guessing remains as limiting the resource usage.

In the paper it is adviced to not set the compress=binary as global l would advice to set it global unless using direct attached SSD's. Small datasets have less time the overhead time may be very high in percentage but in real time measurements not the real bad ones. One forgotten to be set for a big file can be dramatically in real time. With 9.4 also utility file may get better tuned at Buffering/sizing level. All kind of things are changing in time

[Hercules]

Hello Jaap

Thanks for the reply.

I have started with the base SAS, and after getting a good understanding of sas coding, then only I jumped on to SAS DI, not before that.

Yes I know to compress a physical table, or to set readbuf size or insertbuf and all the performance enhancing settings.

But my question is very simple.

If I run a job, or a code, SAS creates a work library which is session oriented and by default all the data sets which we create like

Data temp ;

Will be created and read as work .temp

Now I know that when ever a session starts, autoexec.sas initializes the session and it creates a work library for us. This is done by workinit system option I guess.

But I have gone through many documents, but none mention that how it is deleted if a session closed neatly and resides if it is not.

I hope now I made my query clear.

[Patrick]

The left-over work files from crashed sessions are something which needs to be cleaned out from time to time for not filling up the disk(s) for Work with clutter.

SAS(R) 9.4 Companion for UNIX Environments, Fourth Edition

[Patrick]

SAS temporary tables (WORK tables) are stored in a temporary folder area on disk which SAS creates during invocation and cleans out when shutting down.

To better understand how SAS works it's really worth to spend some time and read through: SAS(R) 9.4 Language Reference: Concepts, Fourth Edition

[jakarman]

For SAS DI I gave the links as the generated DI jobs are clearing temporary tables as part of the generated  code.