BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Sports_addict
Calcite | Level 5

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;

 

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

3 REPLIES 3
ballardw
Super User

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
Super User

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
Calcite | Level 5

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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