joining pass-through query to SAS work table

[DanD999]

Someone has asked me this question. I'm not sure that I see the point if it works like I think it does. If I create a SAS work table like this:

proc sql;

    create table work.worktable as

    select myid

from data.listofids;

run;

And then want to join it to some data that I get from say an Oracle server like this:

proc sql;

connect to oracle (user=&dbuser. pw=&dbpass. dsn="SERVICES" );

            create table mbr_programs as

            select * from connection to oracle (

                        select m.myid, startdt, enddt, volume

                        from work.worktable wt

                        join

(

  select myid, startdt, enddt, volume from db.mb

) as m

                        on m.mbr_id=wt.mbr_id

)

;

disconnect from oracle;

quit;

I know if I put the worktable in the body of the query to the server it won't recognize it but is there any other way to do it? It seems like all the rows from the db.mb table are going to be returned first and then the join to the work table if it will work at all. Which seems pointless if the objective is normally to let the server do the work and reduce the rows coming back to SAS. But I have to ask.

Thanks

[Steelers_In_DC]

I will probably not use the correct verbiage but hopefully you'll understand.  The work table is stored in SAS.  You can think of that on your desktop.  The oracle table is stored in some oracle location somewhere else.  You can bring that into your work library and then join, if you are connected to oracle and pulling two tables that are both stored in oracle you can do all of your joins within oracle before pulling them down to  your desktop.  This can be faster depending on resources.  In your example you are reaching out to oracle and asking it to recognize a table that is stored locally.  Think of it as trying to access something at work that is stored on your desktop at home.  It's two separate locations that will not communicate.

Hope this helps,

Mark

[DanD999]

Mark Johnson wrote:

I will probably not use the correct verbiage but hopefully you'll understand.  The work table is stored in SAS.  You can think of that on your desktop.  The oracle table is stored in some oracle location somewhere else.  You can bring that into your work library and then join, if you are connected to oracle and pulling two tables that are both stored in oracle you can do all of your joins within oracle before pulling them down to  your desktop.  This can be faster depending on resources.  In your example you are reaching out to oracle and asking it to recognize a table that is stored locally.  Think of it as trying to access something at work that is stored on your desktop at home.  It's two separate locations that will not communicate.

Hope this helps,

Mark

Yes, I agree. I was talking to someone else about this and this is what I was saying. Now I think that what he's saying is to create the worktable as a temp table in Oracle and then use it which will work. It's been a long time since I've done that. Doesn't that require a libname that points to the oracle server?

[Steelers_In_DC]

I would pull the oracle table into SAS as a work table.

[DanD999]

Mark Johnson wrote:

I would pull the oracle table into SAS as a work table.

In some cases that would not be very efficient. Some tables have a few hundred million rows.

[SASKiwi]

There are a couple of techniques you can consider both of which are highly efficient:

1) If the list of unique values of myid is small you can construct a WHERE statement in a macro variable and apply it in Oracle. The restriction is a macro variable can only be up to 32K characters long.

2) You can upload your SAS worktable to Oracle and do the join there.

[DanD999]

SASKiwi wrote:

There are a couple of techniques you can consider both of which are highly efficient:

1) If the list of unique values of myid is small you can construct a WHERE statement in a macro variable and apply it in Oracle. The restriction is a macro variable can only be up to 32K characters long.

2) You can upload your SAS worktable to Oracle and do the join there.

Number 1 is out because of what I said above that some tables have hundreds of millions of row. Number 2 is more feasible. Thanks to everyone who responded.