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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.