Have a look at the help topic "SAS/ACCESS Data Set Options" in the docs--there's a DBKEY option that will likely be of use to you.

I *think* that under the covers it's creating a parameterized query on the mssql table based on your join condition, and cycling through your SAS table row by row executing it. But I'm not sure...

HTH,

-Roy

-----Original Message----- From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Jeremy Kemp Sent: Thursday, May 20, 2004 3:26 AM To: SAS-L@LISTSERV.UGA.EDU Subject: Joining SAS Table and SQL Server Table

I have a SAS table that I want to join with a table in a MS SQL Server database based on a claim key - this is an indexed column in the SQL Server table. The number of rows in the SAS table will vary from a few rows to a several thousand rows. There will be tens of thousands of rows in the SQL Server table but there will only be at most of few hundred matches between the two tables (for the query that I need).

I need some help as to what is the best method.

I know I can create a view of the SQL Server table and then join the two with SQL. Will this make any use of the index in SQL Server?

Another alternative is to load the SAS table into a temporary table in SQL Server and do the join within SQL Server. This would make use of the index but seems a bit wasteful.

Has anyone done anything like this with SQL Server or any other RDBMS?

Thanks

Jeremy