proc sql select on a variable pair(tuple)

[bill0101]

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

[bill0101]

I have found a workaround simply using "proc sort + data merge"

It's so simple as if I was such a blockhead. Damn it ~( ￣ c￣)y▂ξ

====================================================

/* most convenient using proc sort + data merge !!!*/

proc sort data=A; /* sort A */

  by ID1 ID2;

run;

proc sort data=list; /* sort list */

  by ID1 ID2;

run;

data A_merge; /* output when (ID1,ID2) is in list dataset*/

  merge A list(in=b);

  by ID1 ID2;

  if b=1 then output;

run;

================================================

[LinusH]

Hope this works for you, still unclear in your description on what you want. So ID1 and ID2 are in fact a combined key?

Your solution should work, just be aware if there exists duplicate rows in you look up table, it could generate some undesired results.

With a combined key, a simple inner join would also do it for you, and could be more efficient performance-wise, if your id columns are indexed.

[Jaheuk]

always concatinate to one, if numeric use put format:

sql;

table A_list as

* from A ID1!!ID2 in (select ID1!!ID2  from list); /* select according to a variable tuple */;

GreetZ,

Herman

[LinusH · Accepted solution]

This would work great for most situations, but it could retrieve to many records depending on the contents of the ID columns. It would be safer to insert a separator with a char that should not exist in the ID columns:

proc sql;

create table A_list as

  select *

     from A

     where catx('#',ID1,ID2) in (select catx('#',ID1,ID2)  from list);

quit;

[bill0101]

The original purpose of my post is to confirm whether a "query using variable tuple" syntax exists or not.

Since SAS proc sql does not have "with" syntax so i cannot do it like this post

sql - Copy the Value from the column which has the min value in a given observation - Stack Overflow

Concatenation does work for this particular dataset. However, it cannot distinguish between something like AB!!A and A!!BA.

i.e. it is not robust to data characteristics.

I think CatX is OK as long as a correct spacer character is chosen. Therefore I regard it as perfectly correct.

Thank you for all your help anyway

Bill