BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Hercules
Fluorite | Level 6

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

View solution in original post

15 REPLIES 15
LinusH
Tourmaline | Level 20

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.

Data never sleeps
Hercules
Fluorite | Level 6

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
Barite | Level 11

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
Fluorite | Level 6

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
Fluorite | Level 6

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
Barite | Level 11

If that what you're into it

then check this old thread

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

Reeza
Super User

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
Fluorite | Level 6

Thank you so much Reeza.

This was the answer I was looking for.

I will read more about workterm now.

jakarman
Barite | Level 11

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

---->-- ja karman --<-----
Hercules
Fluorite | Level 6

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
Opal | Level 21

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
Opal | Level 21

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
Barite | Level 11

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

---->-- ja karman --<-----

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 15 replies
  • 3038 views
  • 0 likes
  • 7 in conversation