topic Re: "Where" type search in other table in SAS Programming

"Where" type search in other table

polpel — Wed, 02 Oct 2019 15:03:42 GMT

Hello,

I need help with something.

Basically, I've got a huge table containing IDs, and what I would like is to filter this table so that I only have the IDs that are listed in another table. I want to do this because I don't want a giant "where" statement in the middle of my code.

But what I'm looking to do is like :

set have where (ids are listed with id reference table).

Is there any way to do this?

Thanks in advance!!

Re: "Where" type search in other table

Reeza — Wed, 02 Oct 2019 15:14:22 GMT

proc sql;
create table want as
select *
from have
where name in (select name from sashelp.class);
quit;

SQL is better for this type of functionality.

Otherwise a MERGE can also do this.

@polpel wrote:

Hello,

I need help with something.

Basically, I've got a huge table containing IDs, and what I would like is to filter this table so that I only have the IDs that are listed in another table. I want to do this because I don't want a giant "where" statement in the middle of my code.

But what I'm looking to do is like :

set have where (ids are listed with id reference table).

Is there any way to do this?

Thanks in advance!!

Re: "Where" type search in other table

hashman — Thu, 03 Oct 2019 07:17:00 GMT

@polpel:

There're no fewer than 20 different methods of doing this in SAS.

@Reeza has shown you one, perhaps the simplest.
If you have a "huge" base table and a rather short reference list, an inner join could be better since, unlike the @Reeza's subquery, it can hash the list and look it up for every row in the huge table without sorting the latter.
Another option is to stick the keys from the reference table in to a hash table in memory and look it up for every record from the base table in the DATA step.
If your reference list is static and you're going to search it often, perhaps the best option is to create an in/format using the reference file's keys as the in/format input with something like 1 or "1" as the in/format response. Then the in/format can be used in the WHERE clause, be it the DATA step or SQL.

Show samples of your two files, and you can be assured that plenty of folks here will show you concrete ways of how any of the above (and more) can be done.

Kind regards

Paul D.