topic How to achieve array function by using SQL in SAS Programming

How to achieve array function by using SQL

tulip — Fri, 06 Nov 2009 03:28:04 GMT

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.

Re: How to achieve array function by using SQL

LinusH — Fri, 06 Nov 2009 11:16:20 GMT

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

Re: How to achieve array function by using SQL

tulip — Sat, 07 Nov 2009 04:10:14 GMT

Hi, Linus:

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

Nice weekend, Tulip