BookmarkSubscribeRSS Feed
juanvg1972
Pyrite | Level 9

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,

3 REPLIES 3
LinusH
Tourmaline | Level 20

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

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.

Kurt_Bremser
Super User

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.

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 Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 809 views
  • 6 likes
  • 4 in conversation