BookmarkSubscribeRSS Feed
MikeAAC
Calcite | Level 5

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;

7 REPLIES 7
Shmuel
Garnet | Level 18

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
Calcite | Level 5

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
Garnet | Level 18

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
Calcite | Level 5

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
Calcite | Level 5

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

MikeAAC
Calcite | Level 5

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

LinusH
Tourmaline | Level 20
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 532 views
  • 0 likes
  • 3 in conversation