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

Dataset A: 

AtIssue.JPG

 

Dataset B: 

PotentialPeers.JPG

 

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: 

 

Answer.PNG

 

Dataset A is maybe ~100 rows by 20 columns

Dataset B is maybe 1 million rows by 20 columns

 

Thank you!! 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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;

View solution in original post

5 REPLIES 5
christinakwang
Obsidian | Level 7

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
PROC Star

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
Obsidian | Level 7

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);

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 5 replies
  • 888 views
  • 1 like
  • 3 in conversation