topic Proc SQL defining table as object in second query in SAS Enterprise Guide

Proc SQL defining table as object in second query

atljogger — Sat, 21 Dec 2013 02:42:34 GMT

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;

Re: Proc SQL defining table as object in second query

PGStats — Sat, 21 Dec 2013 04:25:39 GMT

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.

Re: Proc SQL defining table as object in second query

Tom — Sat, 21 Dec 2013 14:11:17 GMT

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;

Re: Proc SQL defining table as object in second query

atljogger — Sat, 21 Dec 2013 17:12:01 GMT

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