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;
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.
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.
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.
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 🙂
An alternative (I haven't test yet) would be to pass the ODBC pass-thru not in query but in subquery.
Bad Alternative as I won't to keep most of the vars in the table in ODBC
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.