SAS data Integration minimize ressource use

[ANLYNG]

Hi,

When we execute our sas di flows we see a huge sas work usage (1 TB) even though we generally enable  the "Create as View" option on transformation output ports in DI Studio? and in the end/table loader write output to SQL database. Our system has crashed several times because of insufficient space in work area.

My question is why "create as view" seems to use a lot af SAS work space? what is best practice using "create as View" or options to effective write to a SQL database via Standard Table loader? 

What is best practice ?

We are running 9.4 M6 on Linux

Thanks in advance.

[Kurt_Bremser]

Creating a SQL view takes next to no time and almost no resources at all, but using the view will mean it has to be executed, and depending on the complexity, this will make heavy use of WORK and UTILLOC locations, as is typical for SAS SQL. If you use the view in an already complex query, you'll quickly get into trouble.

The usual remedies are

• prepare your datasets in steps, with creating intermediate datasets in simpler SQL queries
• sort in a separate step, so SQL does not have to do it on the fly
• replace SQL queries with other means (e.g. summarize with PROC MEANS instead of SQL)

If that does not fix it, you may have to resort to a user-written transformation as a last measure.

[ANLYNG]

Thanks for your answer. The suggestion with intermediate datasets/steps in the SAS DI Job do you mean that it there should be outputted as a work table (and not create as view) ?

[Kurt_Bremser · Accepted solution]

Yes. Additionally, consider to create the intermediate datasets in a permanent library (so you avoid cluttering up WORK) and remove them in a later step.