BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
bill0101
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
LinusH
Tourmaline | Level 20

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;

Data never sleeps

View solution in original post

5 REPLIES 5
bill0101
Calcite | Level 5

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
Tourmaline | Level 20

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.

Data never sleeps
Jaheuk
Obsidian | Level 7

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
Tourmaline | Level 20

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;

Data never sleeps
bill0101
Calcite | Level 5

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 Smiley Happy

Bill

sas-innovate-2024.png

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.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 4488 views
  • 7 likes
  • 3 in conversation