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;
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
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;
Thank you both. Embedding the sub-query worked perfect.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.