Joining views with indexes in proc SQL

2010-05-26T21:51:34Z

Hi All

I have two views, app, and app_status. Each view simply concatenates monthly datasets like so:

create view app as 
select * from app_200901 outer union corresponding 
select * from app_200902 outer union corresponding 
...
select * from app_201004 outer union corresponding     
select * from app_201005;


create view app_status as
select * from app_status_200901 outer union corresponding 
select * from app_status_200902 outer union corresponding 
...
select * from app_status_201004 outer union corresponding     
select * from app_status_201005;

Each TABLE (ie. app200901 or app_status_200901, etc.) is indexed on app_id. When querying either view the indexes on the tables are used. For example the following is fine:

select *
from app
where app_id = 123

However, if I cannot find a way to make SAS use the indexes when joining the two views together. For example:

select a.*, b.status
from app a
join app_status b on b.app_id eq a.app_id

In my mind this should work. In reality it doesn't =). The views contain almost 100 million records each so performance is important.

Any suggestions on how I can join these tables faster!?

Thanks Rob

Is a Hash object faster than an Array?

2009-10-21T01:51:23Z

Could anyone explain why an array will always be faster than a hash object described in SAS Prog3 course note?

performance Questions - RunSubmit.com

Joining views with indexes in proc SQL

Is a Hash object faster than an Array?