SAS ACCESS: Universal SAS Methods to Access Just About Any Data, Anywhere

[MikeAAC]

How can I can query a view in my odbc by selecting only Accounts ID that are store in a SAS table in Work lib. Running code below, I got an error message but not if in a where clause i specified a value like where Id ='e01'. 

proc sql;
CONNECT TO odbc as mydb (datasrc="..." authdomain=_auth);
CREATE TABLE Id_from_odbc_vw AS
select * from connection to mydb
      (
          select Id, Name, DOB
          from input_vw

         where  Id in (select  AccountId from sas_tbl_wk_lib)

      );

DISCONNECT FROM mydb;
QUIT;

[Shmuel]

As much as I know you have to do it in 2 steps:

1) select relevant data from the odbc database into sas.

2) reselect relevant observartion matching the other table.

[MikeAAC]

Thanks Shemuel but all the relevant ID are in a sas table. Obviously, it is not efficient to pull all the ODBC into a SAS table. I just need a pass thru query that pull from ODBC, the same ID I have in my SAS table. 

[Shmuel]

If you want to select small amount of records you can (memory dependent) you can:

1) select record_id (s) into a macro variable (record_ids_list) separated by comma

2) 

proc sql;
CONNECT TO odbc as mydb (datasrc="..." authdomain=_auth);
CREATE TABLE Id_from_odbc_vw AS
select * from connection to mydb
      (
          select Id, Name, DOB
          from input_vw

         where  Id in (&record_ids_list)

      );

DISCONNECT FROM mydb;
QUIT;

otherwise, maybe someone else knows a better method.

[MikeAAC]

I want basically to select almost 10 Mio ids. puttng them in a macro list does not seem to be the way but thanks for trying to help. Maybe Chris Hemedinger can find another way 🙂

[MikeAAC]

An alternative (I haven't test yet) would be to pass the ODBC pass-thru not in query but in subquery. 

[MikeAAC]

Bad Alternative as I won't to keep most of the vars in the table in ODBC

[LinusH]

Thw last couple of monrhe the forum has been cluttered with similar question. By doing a search first you could save yourself and others time.
That said, pass through can never talk to local SAS data.
Either upload the required data first.
Or join via a libname connection, and use DBKEY= to optimize the query.