Hello, My question is how to select data on a variable pair (i.e.(ID1,ID2) or more than 2) in a sub-expression of proc sql? The concept is where (ID1, ID2) in (select (ID1, ID2) from list) but the above syntax just doesn't work. Here's the complete attempted test code and log message: (btw, the environment is SAS 9.3 on win7 x64) /***** test code: select on variable pairs *****/ /* data (ID1, ID2, x) */ data A; input ID1 $ ID2 $ x @@; datalines; A X 1 A X 2 A X 3 A Y 1 A Y 2 A Y 3 B X 1 B X 2 B X 3 B Y 1 B Y 2 B Y 3 ; run; /* wanted record: (ID1, ID2)=(A,X) or (B,Y)*/ data list; input ID1 $ ID2 $ @@; datalines; A X B Y ; run; /* attempted code */ proc sql; create table A_list as select * from A where (ID1, ID2) in (select (ID1, ID2) from list); /* select according to a variable tuple */ quit; /***** end test code *****/ log message: 4305 proc sql; 4306 create table A_list as 4307 select * from A 4308 where (ID1, ID2) in (select (ID1, ID2) from list); - - 79 79 ERROR 79-322: Expecting a (. 4308! where (ID1, ID2) in (select (ID1, ID2) from list); ---- - 79 79 ERROR 79-322: Expecting a ). 4309 quit; NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE SQL used (Total process time): real time 0.00 seconds cpu time 0.00 seconds Thanks in advance! Bill
... View more