BookmarkSubscribeRSS Feed
polpel
Fluorite | Level 6

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!!

2 REPLIES 2
Reeza
Super User
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!!


 

hashman
Ammonite | Level 13

@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.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 2 replies
  • 355 views
  • 0 likes
  • 3 in conversation