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.
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.
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;
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;
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.
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.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.