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

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

christinakwang — Tue, 31 Jan 2017 07:01:25 GMT

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

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

Reeza — Tue, 31 Jan 2017 06:37:01 GMT

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

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

christinakwang — Tue, 31 Jan 2017 06:37:57 GMT

Please do teach 🙂 (I am such a beginner)

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

christinakwang — Tue, 31 Jan 2017 07:00:46 GMT

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?

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

Astounding — Tue, 31 Jan 2017 07:38:11 GMT

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;

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

christinakwang — Tue, 31 Jan 2017 22:06:46 GMT

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