Proc SQL defining table as object in second query

[atljogger]

I'm new to SAS so apologize for the newbie question - I can't figure this task out.    I have a  two part query - first part creates a table of records and second part uses that table.  Basically I want to tell the second query to go look up the data in the first table and then match on it in a second table.

Simplistic example below - I create table 'car_list' with a single column of data 'model_id'.  I then match only those model ID's in a second table to pull sales and locations.  This second query creates and outputs 'sales_summary' table.   The first process correctly creates 'car_list'', but the second query returns error message Object 'car_list' does not exist.  How do I retrieve the model_id in 'car_list' table to use in 2nd process?  Does it go in FROM section or I do have to do some kind of In List function?  *This is several million rows so copy/pasting in the output from Query 1 is not an option

proc sql;
    connect to...;

create table car_list as ...(

  select  model_id

from  datasource1.model_list

where model_id in (a,b,c,d,e)

);quit;

proc sql;
    connect to...;

create table sales_summary as ...(
  select
        sum(a.sales) as Sales,
        count(a.locations) as Locations      

from
        datasource2.sales a,
        car_list b

where  a.model_id = b.model_id

group by 1,2

);quit;

[PGStats]

Not sure I understand what you are doing. The problem seem to be that you are asking a server to join a table that was created on your local machine (car_list) with a table that resides on the server (sales). The server doesn't know where your SAS table is. If both queries are pass-through queries run on the same database then they could be combined into a single query that would be run on the server without creating the local table.

PG

[Tom]

So this is poor example as to fix this just copy the WHERE clause from the first query to the second.

But in the general case you can embed the first query as a sub query in the second.  This syntax will work in SAS, but you need to check whether it will work in your database system.

proc sql;

connect to ... as db (...);

create table sales_summary as

  select * from connection to db

( select sum(a.sales) as Sales

       , count(a.locations) as Locations

  from datasource2.sales a

     , (

         select model_id

         from datasource1.model_list

         where model_id in (a,b,c,d,e)

       ) b

  where a.model_id = b.model_id

  group by 1 ,2

)

;

quit;

[atljogger]

Thank you both.  Embedding the sub-query worked perfect.