topic Re: SAS data Integration minimize ressource use in SAS Data Management

SAS data Integration minimize ressource use

ANLYNG — Thu, 19 May 2022 08:32:38 GMT

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.

Re: SAS data Integration minimize ressource use

Kurt_Bremser — Thu, 19 May 2022 08:48:04 GMT

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.

Re: SAS data Integration minimize ressource use

ANLYNG — Thu, 19 May 2022 08:53:41 GMT

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) ?

Re: SAS data Integration minimize ressource use

Kurt_Bremser — Thu, 19 May 2022 09:13:12 GMT

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.