Help using Base SAS procedures

How effecient are correlated sub-squeries for large datasets (Proc SQL)? Is it a common practice among sas users?

Reply
Contributor
Posts: 40

How effecient are correlated sub-squeries for large datasets (Proc SQL)? Is it a common practice among sas users?

My coding style in SAS and SQL is writing step wise code with comments rather than complex sub-squeries. I prefer doing multiple joins rather than one complex join within a subquery.

Recently  I have seen some code with massive Sub-queries and multiple joins which honestly I found hard to follow. I must mention it was by SQL coders and not SAS users.

My question is - Are correlated sub -queries very commonly used in SAS - specially with large tables ( 100k+ rows)? Do you guys come across this quite often?

I understand there is nothing wrong or right in this but my aim is to know what is the best practice

Thanks, as always.

Sachin   

Super User
Posts: 5,427

Re: How effecient are correlated sub-squeries for large datasets (Proc SQL)? Is it a common practice among sas users?

Posted in reply to sachin01663

Can't tell if my experience applies only to SAS. In general, I think correlated sub-queries can be complex, and for large tables, hard to optimize.

Indexing on the key columns may help to make speed it up.

Often these queries are used for updating target data with a transaction table. Even if I generally like SQL, I think that in these cases, a data step approach (modify by) is often much more efficient.  

Data never sleeps
Super User
Posts: 10,023

Re: How effecient are correlated sub-squeries for large datasets (Proc SQL)? Is it a common practice among sas users?

Posted in reply to sachin01663

My question is - Are correlated sub -queries very commonly used in SAS - specially with large tables ( 100k+ rows)?



No. SQL is not suited for large tables, and even it is not as efficient as cartesian product.

It is advantage for SAS to process large table.

Ksharp

Super Contributor
Posts: 334

Re: How effecient are correlated sub-squeries for large datasets (Proc SQL)? Is it a common practice among sas users?

Posted in reply to sachin01663

I dont have hard evidence for this and it might also fall under the category of "your mileage might vary", but given the gains in processing power and memory, SAS is almost always bottle necked at I/O (ie writing info to disk, temporary or permanently). Given this the less writing to disk you do the better, so sub queries should reduce some of the I/O. I have seen this in my case (complex queries from relational databases) but as mention by it probably depends on what the task is. Some of our more complex queries are made because we are writing / verify queries for a SQL Server Application our Developers are making, which means the query needs to be as platform independent as possible (ie no data steps or procs).

I would say even after 10 years of writing SQL code in SAS, I rarely start off building a complex query directly but build the pieces and see if it makes sense to combine, just because I find it easier to track down where I have gone wrong that way. I do believe there is a trade off between readablilty of the code and using highly complex SQL queries (4 or more levels deep).

I would be interested in hearing others comments.

EJ

Super User
Posts: 3,252

Re: How effecient are correlated sub-squeries for large datasets (Proc SQL)? Is it a common practice among sas users?

Posted in reply to sachin01663

My impression is that SAS's SQL optimiser has improved a lot over recent SAS versions, that is between 9.1 and 9.3 (and way better than Version 8). I found 9.1 to be not so good with complex SQL especially sub-queries, but recently I have tried writing steps with 5 or 6 sub-queries in SAS 9.3 and found performance to be very good. But as always only testing with your specific requirements will prove it one way or the other. I follow the EJ approach also!

Contributor
Posts: 40

Re: How effecient are correlated sub-squeries for large datasets (Proc SQL)? Is it a common practice among sas users?

Posted in reply to sachin01663

Thank you guys.

I agree with esjackso1 and LinusH. I hardly use complex SQl queries so wanted to understand what am I missing on.

Ask a Question
Discussion stats
  • 5 replies
  • 229 views
  • 0 likes
  • 5 in conversation