Query using a list variable from a different dataset in Proc SQL

[Sports_addict]

I have a dataset (dataset1) with 10 columns. I would like to use one of the columns (ID) as an input in proc sql to query a cloud server to retrieve more info.

This is what I have so far, but it doesn't work:

PROC SQL;

CONNECT TO ODBC (DSN='database_name');

CREATE TABLE WORK.new_table AS

SELECT *

FROM connection to odbc (select * from cloud_table

where (col_name IN (SELECT COL1 FROM WORK.DATASET1)) );

disconnect from odbc;

quit;

If I were to write exact same proc with each uniqueID in ' ' then it works, for example:

PROC SQL;

CONNECT TO ODBC (DSN='database_name');

CREATE TABLE WORK.new_table AS

SELECT *

FROM connection to odbc (select * from cloud_table

where (col_name IN ('id1',

'id2',

'id3', etc.

)

) );

disconnect from odbc;

quit;

[ballardw]

One common way to select records from a second data set where the value of one or more variables is used to select is a left (or right) join.

A brief example:

data work.one;
   sex='M';
run;

proc sql;
   create table work.example as
   select b.*
   from work.one as a
        left join
        sashelp.class as b
        on a.sex=b.sex
   ;
quit;

The data set work.one is just to create something to look up. The "as a" and "as b" are alias names so I can reference the data sets easier using aliasname.variablename. The ON says to select the records where the value of sex matches in both data sets and keep all the variables from data set alias b in the result.

Your data set can have multiple values like id to match on and you will get all of the records from the second set.

[Reeza · Accepted solution]

Since it looks like you're using SQL Pass through you cannot use the work data sets. SQL Pass through means all the work is handled on the server, and the SQL needs to be in the correct style for the server, not SAS SQL. 

A common workaround is to create macro variables out of the list. Since it's only 10 IDs it works fine, if its bigger it becomes an issue. Macro variables have a limit of 64K characters. 

*for character variables;
proc sql noprint;
select quote(name) into :name_list separated by ", " 
from sashelp.class 
where sex='F';
quit;

%put &name_list;


*for numeric variables;
proc sql noprint;
select distinct age into :age_list separated by ", " 
from sashelp.class 
where sex='F';
quit;

%put &age_list;

Then you can use the macro variable in the WHERE statement.

where ID in (&name_list);

---

@Sports_addict wrote:

I have a dataset (dataset1) with 10 columns. I would like to use one of the columns (ID) as an input in proc sql to query a cloud server to retrieve more info.

This is what I have so far, but it doesn't work:

PROC SQL;

CONNECT TO ODBC (DSN='database_name');

CREATE TABLE WORK.new_table AS

SELECT *

FROM connection to odbc (select * from cloud_table

where (col_name IN (SELECT COL1 FROM WORK.DATASET1)) );

disconnect from odbc;

quit;

If I were to write exact same proc with each uniqueID in ' ' then it works, for example:

PROC SQL;

CONNECT TO ODBC (DSN='database_name');

CREATE TABLE WORK.new_table AS

SELECT *

FROM connection to odbc (select * from cloud_table

where (col_name IN ('id1',

'id2',

'id3', etc.

)

) );

disconnect from odbc;

quit;

---

[Sports_addict]

Thanks a bunch! I was able to retrieve data from the table with only minor changes. I had to use quote((strip(name), "'") into :name_list this let me search the sql table. Note it's single quote inside " ".