DATA Step, Macro, Functions and more

Using SAS views in ETL processes

Reply
Frequent Contributor
Posts: 122

Using SAS views in ETL processes

Hi,

I have to made some ETL process in SAS, typical process in which some intermediate tables were created:

input file -> (read) -> temp1 -> (transform) -> temp2 -> ........ -> tempN -> (load) -> database

Are sas views suitable in the transform steps??. I think it can reduce I/O, but what about CPU time?

I woud like to know pros and cons of using views in that type of processes.

Thanks in advance,

Super User
Posts: 5,429

Re: Using SAS views in ETL processes

Posted in reply to juanvg1972

Usually I/O is the bottleneck that you need to worry about. There is no real truth here, so many different use cases. So start with views and evaluate your CPU consumption as you go.

Data never sleeps
Respected Advisor
Posts: 4,173

Re: Using SAS views in ETL processes

Posted in reply to juanvg1972

The "problem" with views is that it combines all the execution into the node where you actually create a physical table. That makes it sometimes much harder to debug in case of error.

Views can help to improve performance but if nesting too many views then performance can also decrease. You basically need to test what performs better.

Using DI I normally create tables and test step by step. If I need to improve performance then I might change some of the physical tables to views and look what impact this makes on performance.

Super User
Posts: 7,799

Re: Using SAS views in ETL processes

Posted in reply to juanvg1972

If you use a view to extract a subset of data (vertical and/or horizontal) from a dataset, and use that three times, you will read the whole sourcedata three times. If instead you create a smaller (temporary?) dataset once and use that three times, you will be more efficient. This will become even more evident if the view contains a join.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Ask a Question
Discussion stats
  • 3 replies
  • 218 views
  • 6 likes
  • 4 in conversation