Revised Q: output any row from Dataset B whose x matches x in Dataset A within a group

[christinakwang]

Dataset A: 

Dataset B: 

In this case, I'm hoping to create some procedure (could be a macro,... for example, let's say macro below)

%MACRO findpeers(Fund_name) 

....

such that if "Fund_name" = "Hairspray", then it will go through and output every row in Dataset B that has a matching "Class" for any of the rows -- that is, any row in Dataset B that has GG, HC, or NK as the class gets outputted as Dataset C.  

So %findpeers (Hairspray) should output the following dataset: 

Dataset A is maybe ~100 rows by 20 columns

Dataset B is maybe 1 million rows by 20 columns

Thank you!! 

[Reeza]

Why a macro? Wouldn't a SQL join be sufficient?

[christinakwang]

Please do teach 🙂 (I am such a beginner)

[christinakwang]

OK I revised the question. I realized I wasn't representing the question accurately. 

In case of the new question, not sure if a sql join would work...because it's not exactly matching anymore? 

[Astounding · Accepted solution]

First, get the program working for Hairspray without any macro language.  You can always convert it to a macro afterwards.

Here's the idea of a SQL join.  You may need to correct the syntax, however!

proc sql noprint;

create table results as select * from B 

where class in (select distinct class from A where a.fund_name="Hairspray");

quit;

[christinakwang]

Thanks!

The final code ended up being: 

%MACRO findpeers_inclusive(fundname);

proc sql; 
	create table atissue.&fundname._peers_inclusive as 
		select * 
		from atissue.potentialpeers
	where class in (select distinct class from atissue.atissue where fund_name = "&fundname");
quit;

%MEND findpeers_inclusive;

%findpeers_inclusive (Hairspray);
%findpeers_inclusive (Hamilton);