DATA Step, Macro, Functions and more

How to achieve array function by using SQL

Reply
Occasional Contributor
Posts: 18

How to achieve array function by using SQL

Hi, pros:

I have

data1: with a variable (Destination) with several values (123, 281, ...) ;
data2: structure as follows
id Des1 Des2 Des3
1 123 234 233
1 234 281 341
2 ....

Goal: if a person goes to any one of Destination by looking at 3 field of Des1, Des2, Des3, I would like to subset.

I can do it by using array and macro.

Do you think SQL can do a better job? If so, how. Please advise. Thanks.

Tulip.
Super User
Posts: 5,429

Re: How to achieve array function by using SQL

I don't know about better, I came to think of using IN sub-queries:

select data2.*
from data2
where data2.Des1 in(select Destination from data1)
or data2.Des2 in(select Destination from data1)
or data2.Des3 in(select Destination from data1)
;

If your destination table is not huge, it would be feasable.

/Linus
Data never sleeps
Occasional Contributor
Posts: 18

Re: How to achieve array function by using SQL

Hi, Linus:

Thanks a lot. It is much better than the way I used before.

Nice weekend, Tulip
Ask a Question
Discussion stats
  • 2 replies
  • 114 views
  • 0 likes
  • 2 in conversation