Help using Base SAS procedures

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

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

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

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;

 


Accepted Solutions
Solution
‎06-08-2018 09:52 AM
Super User
Posts: 23,694

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

Posted in reply to Sports_addict

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;

 


 

View solution in original post


All Replies
Super User
Posts: 13,517

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

Posted in reply to Sports_addict

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.

Solution
‎06-08-2018 09:52 AM
Super User
Posts: 23,694

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

Posted in reply to Sports_addict

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;

 


 

New Contributor
Posts: 2

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

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 " ".

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 154 views
  • 3 likes
  • 3 in conversation