Date:         Tue, 1 Jun 2010 12:07:15 -0400
Reply-To:     "W. Matthew Wilson" <matt@TPLUS1.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "W. Matthew Wilson" <matt@TPLUS1.COM>
Subject:      Join SQL table with a SAS dataset?
Content-Type: text/plain; charset=ISO-8859-1

I have two lists of employees.  One list is stored in a SQL database,
with one row per employee.  The other list is stored in a SAS dataset,
also with one row per employee.

I want to join the two lists together and find all the employees in
the SQL database and not in the SAS dataset.

I tried this:

proc sql;

connect to OLEDB as SQLCONN (init_string="...");

create table nonmatched_employees as
select * from connection to SQLCONN
(
    select sqltable.id
    from sqltable
    left join sasdataset
    on sqltable.id = sasdataset.id
    where sasdataset.id is null
);

quit;

The idea is that I want to left join all the rows in the SQL table
with their SAS counterparts, and only keep the SQL table rows that
have no match.  This fails though.  I get this cryptic error:

ERROR: Describe error: IColumnsInfo::GetColumnInfo failed. : Deferred
prepare could not be
       completed.: Statement(s) could not be prepared.: Invalid object name ...

Am I going about this in the wrong way?  How else can I compare a SAS
dataset with a SQL table?  Do I need to convert the SQL table into SAS
first?

Matt

--
W. Matthew Wilson
matt@tplus1.com
http://tplus1.com