How to select observations in the whole sample based on a subsample ?

[Phil_NZ]

Hi all SAS Users,

I have the dataset have:

gviidkey	x1	    x2	ave_mar_cap
001166_01W	0	    3	    781881399.22
001166_02W	0	    3	    782818024.77
001166_01   0       3       59988888.13
001491_01W	0	    3	    211021885.49
001855_03W	0	    3	    7490455.1517
001932_01W	0	    3	    6966150208.8
001945_01W	0	    3	    3661586156.1
002162_01W	0	    3	    2576720.9485
002162_02W	0	    3	    2163731.9025
002338_01W	0	    3	    772042538.92
002410_01W	0	    3	    79948092816
002411_01W	0	    3	    33749159713
002411_03W	0	    3	    84226468905

And I have the subsample dataset destination

gviidkey	
001166_02W	
001491_01W	
001855_03W	
001932_01W	
002162_01W	
002338_01W	
002410_01W	
002411_03W

What I want is dataset final

gviidkey	x1		x2      ave_mar_cap
001166_02W	0	    3	    782818024.77
001491_01W	0	    3	    211021885.49
001855_03W	0	    3	    7490455.1517
001932_01W	0	    3	    6966150208.8
002162_01W	0	    3	    2576720.9485
002338_01W	0	    3	    772042538.92
002410_01W	0	    3	    79948092816
002411_03W	0	    3	    84226468905

Can you please hint at me on how to sort it out?

Many thanks and warm regards.

My novice code is

proc sql;
create table final as
select a.*, b.gviidkey
from have a left join destination b
on a.gviidkey=b.gviidkey;
quit;

[Kurt_Bremser · Accepted solution]

You don't want a left join, you want an inner join:

proc sql;
create table final as
select a.*
from have a, destination b
where a.gviidkey=b.gviidkey;
quit;

Do not select gviidkey from b, as it is already contained in a.*. This causes a WARNING.

If you want to avoid any sorting, use a hash object:

data want;
set have;
if _n_ = 1
then do;
  declare hash d (dataset:"destination");
  d.definekey("gviidkey");
  d.definedone();
end;
if d.check() = 0;
run;