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

Reply
Occasional Contributor
Posts: 7

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

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;

Trusted Advisor
Posts: 1,837

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

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.

Occasional Contributor
Posts: 7

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

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. 

Trusted Advisor
Posts: 1,837

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

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.

Occasional Contributor
Posts: 7

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

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 :-)

Occasional Contributor
Posts: 7

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

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

Occasional Contributor
Posts: 7

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

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

Super User
Posts: 5,876

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

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.
Data never sleeps
Ask a Question
Discussion stats
  • 7 replies
  • 94 views
  • 0 likes
  • 3 in conversation