topic Re: proc sql select on a variable pair(tuple) in SAS Procedures

proc sql select on a variable pair(tuple)

bill0101 — Tue, 21 Jan 2014 08:50:01 GMT

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

Re: proc sql select on a variable pair(tuple)

bill0101 — Tue, 21 Jan 2014 10:50:00 GMT

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;

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

Re: proc sql select on a variable pair(tuple)

Jaheuk — Tue, 21 Jan 2014 13:38:53 GMT

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

Re: proc sql select on a variable pair(tuple)

LinusH — Tue, 21 Jan 2014 14:00:57 GMT

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;

Re: proc sql select on a variable pair(tuple)

LinusH — Tue, 21 Jan 2014 14:05:28 GMT

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.

Re: proc sql select on a variable pair(tuple)

bill0101 — Tue, 21 Jan 2014 14:08:13 GMT

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