01-24-2015 03:05 AM
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?
01-25-2015 01:34 AM
01-24-2015 02:39 PM
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.
01-24-2015 08:47 PM
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.
01-24-2015 02:37 PM
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?
01-24-2015 08:41 PM
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.
01-24-2015 08:59 PM
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.
01-24-2015 11:03 PM
01-25-2015 01:34 AM
01-24-2015 03:29 PM
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
01-24-2015 08:56 PM
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.
01-25-2015 07:41 PM
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.
01-24-2015 07:48 PM
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
01-25-2015 03:30 AM
For SAS DI I gave the links as the generated DI jobs are clearing temporary tables as part of the generated code.
Need further help from the community? Please ask a new question.