Help using Base SAS procedures

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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 16
Accepted Solution

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

[ Edited ]

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

 


Accepted Solutions
Solution
‎01-31-2017 05:06 PM
Super User
Posts: 5,497

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

Posted in reply to christinakwang

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


All Replies
Super User
Posts: 19,770

Re: Create a macro that outputs rows from Dataset B if its variables x and y match x and y in Datase

Posted in reply to christinakwang

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

Occasional Contributor
Posts: 16

Re: Create a macro that outputs rows from Dataset B if its variables x and y match x and y in Datase

Please do teach Smiley Happy (I am such a beginner)

Occasional Contributor
Posts: 16

Re: Create a macro that outputs rows from Dataset B if its variables x and y match x and y in Datase

Posted in reply to 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? 

Solution
‎01-31-2017 05:06 PM
Super User
Posts: 5,497

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

Posted in reply to christinakwang

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;

Occasional Contributor
Posts: 16

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

Posted in reply to Astounding

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);
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 210 views
  • 1 like
  • 3 in conversation