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

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. 

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

7 REPLIES 7
Reeza
Super User

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

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;

 

 

 

 

Reeza
Super User

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.

BBLOCKOFETT
Calcite | Level 5

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.

Reeza
Super User

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. 

BBLOCKOFETT
Calcite | Level 5
Thank you for all the insight!!
LinusH
Tourmaline | Level 20
????
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

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!

How to Concatenate Values

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.

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
  • 7 replies
  • 5435 views
  • 0 likes
  • 3 in conversation