DATA Step, Macro, Functions and more

How do I reference an Imported file in a PROC SQL WHERE statement?

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

How do I reference an Imported file in a PROC SQL WHERE statement?

If I use the PROC IMPORT statement to import an Excel file, how do I use that file in the WHERE statement in PROC SQL?

 

For example, I imported an Excel file that has three columns (Year, Product Number, Customer ID).


Then, in my PROC SQL WHERE statement, I want my criteria to only be on the Product Number(s) that are in my imported file.

 

I can import the file; however, I am having trouble running the PROC SQL statement for only the Product Number(s) in the import file.

 

Thank you. 


Accepted Solutions
Solution
‎07-05-2016 01:50 PM
Super User
Posts: 19,789

Re: How do I reference an Imported file in a PROC SQL WHERE statement?

Posted in reply to BBLOCKOFETT

First, as mentioned your error isn't from the import procedure but attempting to access your server.

Second, you can't use a SAS table in a pass through query, which is what you're attempting to do. If you create a libname to your DB and then use a standard proc sql you can use the method suggested previously.

View solution in original post


All Replies
Super User
Posts: 19,789

Re: How do I reference an Imported file in a PROC SQL WHERE statement?

Posted in reply to BBLOCKOFETT

When you import the file, you saved the dataset in a location, where you referenced it in the OUT= statement. You can use that name in the rest of your code. 

 

OUT=dataset_name

You can reference this in your SQL query

 

proc sql;
create table want as
select *
from table1
where product_number in (select product_number from dataset_name);
quit;
New Contributor
Posts: 4

Re: How do I reference an Imported file in a PROC SQL WHERE statement?

[ Edited ]

Thank you, Reeza; however, I am still receiving some errors.  After the PROC IMPORT statement, do I need to specify the column names in order for the PROC SQL statement to recognize what I am referencing?

 

After I IMPORT the Excel file, I am then connecting to a DB2 table, is there a join statement that I need to make?

 

The error I am receiving is:

 

ERROR: CLI describe error: [IBM][CLI Driver][DB2] SQL0204N "ZCLMSAS.PAYMENTS1" is an undefined

name. SQLSTATE=42704

 

 

PROC SQL;

CONNECT TO odbc as DB2;

CREATE TABLE AllData AS

SELECT * FROM CONNECTION TO DB2

(

SELECT DISTINCT

DB2TABLE.CUSTOMER.PRODUCT_NUMBER,

DB2TABLE.CUSTOMER.YEAR,

DB2TABLE.CUSTOMER.CUSTOMER_ID

 

FROM

DB2TABLE.CUSTOMER

 

WHERE

PRODUCT_NUMBER in (SELECT PRODUCT_NUMBER FROM PAYMENTS1);

)

;

DISCONNECT FROM DB2;

QUIT;

 

 

 

 

Solution
‎07-05-2016 01:50 PM
Super User
Posts: 19,789

Re: How do I reference an Imported file in a PROC SQL WHERE statement?

Posted in reply to BBLOCKOFETT

First, as mentioned your error isn't from the import procedure but attempting to access your server.

Second, you can't use a SAS table in a pass through query, which is what you're attempting to do. If you create a libname to your DB and then use a standard proc sql you can use the method suggested previously.

New Contributor
Posts: 4

Re: How do I reference an Imported file in a PROC SQL WHERE statement?

Thank you, Reeza.

 

As you suggested, I added my LIBNAME statment at the beginning and was able to run my query with the coding you suggested.

 

Why are SAS tables unable to be referenced in a pass through query?

 

Thank you.

Super User
Posts: 19,789

Re: How do I reference an Imported file in a PROC SQL WHERE statement?

Posted in reply to BBLOCKOFETT

Because pass through literally takes the code and passes it to server to process the query. The server doesn't have the SAS dataset. 

 

It's also why the code is native to the SQL environment, ie MS SQL or Oracle SQL, not SAS SQL. 

New Contributor
Posts: 4

Re: How do I reference an Imported file in a PROC SQL WHERE statement?

Thank you for all the insight!!
Super User
Posts: 5,426

Re: How do I reference an Imported file in a PROC SQL WHERE statement?

Posted in reply to BBLOCKOFETT
????
What happened to the imported data from Excel?
The error you are receiving seems you be from DB2 do contact your dba to figure that one out.
Data never sleeps
☑ This topic is solved.

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

Discussion stats
  • 7 replies
  • 531 views
  • 0 likes
  • 3 in conversation