topic Re: SAS ACCESS: Universal SAS Methods to Access Just About Any Data, Anywhere in SAS Programming

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

MikeAAC — Wed, 11 Apr 2018 14:39:04 GMT

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;

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

Shmuel — Wed, 11 Apr 2018 14:47:01 GMT

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.

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

MikeAAC — Wed, 11 Apr 2018 14:53:38 GMT

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.

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

Shmuel — Wed, 11 Apr 2018 15:12:02 GMT

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

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.

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

MikeAAC — Wed, 11 Apr 2018 16:44:24 GMT

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 🙂

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

MikeAAC — Wed, 11 Apr 2018 16:57:31 GMT

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

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

MikeAAC — Wed, 11 Apr 2018 17:08:46 GMT

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

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

LinusH — Thu, 12 Apr 2018 03:02:25 GMT

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.