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

Hi all, 

 

I have a (small) dataset with a datavariable x, of type string. I want to use its values to select data from another (big) set. Doing an inner join as follows seems to be too time-consuming.

proc sql;
create table aa as
select
a.*
from big a
inner join small b
on a.x = b.x
;
quit;

Is there a better way? For example, first put the x values from the small set into a macro list, and then subset on the big one using a where statement.

 

Thanks in advance.

 

Dimitri

1 ACCEPTED SOLUTION

Accepted Solutions
5 REPLIES 5
Kurt_Bremser
Super User

Use a hash object. No sorting required (the hash sorts internally), will be fastest.

data aa;
set big;
if _n_ = 1
then do;
  declare hash b (dataset:"small (keep=x)");
  b.definekey('x');
  b.definedone();
end;
if b.check() = 0;
run;
PaigeMiller
Diamond | Level 26

If I am understanding you properly, you want to use a RIGHT JOIN instead of an INNER JOIN

--
Paige Miller
drdee
Obsidian | Level 7
Hi Paige,

Thanks for your reply! Yes, it may also be useful to see when some x values are not found in the big set.

Best regards,
Dimitri
Kurt_Bremser
Super User

Instead of the inner join, you can use a WHERE clause in SQL:

proc sql;
create table aa as
  select *
  from big
  where x in (select distinct x from small)
;
quit;
drdee
Obsidian | Level 7
Hi Kurt,

Many thanks for your quick replies! Both work perfectly. I accepted the second as it is easier to understand (for me) what happens.

Best regards,
Dimitri

sas-innovate-white.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 5 replies
  • 923 views
  • 3 likes
  • 3 in conversation