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

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

BBLOCKOFETT — Thu, 30 Jun 2016 20:33:20 GMT

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.

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

Reeza — Thu, 30 Jun 2016 21:11:34 GMT

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;

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

BBLOCKOFETT — Fri, 01 Jul 2016 13:52:51 GMT

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;

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

LinusH — Fri, 01 Jul 2016 22:14:47 GMT

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

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

Reeza — Fri, 01 Jul 2016 22:42:45 GMT

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.

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

BBLOCKOFETT — Tue, 05 Jul 2016 17:50:11 GMT

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.

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

Reeza — Tue, 05 Jul 2016 20:41:07 GMT

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.

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

BBLOCKOFETT — Wed, 06 Jul 2016 18:18:55 GMT

Thank you for all the insight!!