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
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.
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.