Dataset A: data ATISSUE;
infile datalines dsd truncover;
input Fund_name:$14. Year:BEST. Class:$2. Sales:BEST. Index_Flag:BEST. Load:$2. FoF_Flag:BEST.;
datalines4;
Hamilton,2005,CW,1,1,CW,1
Hamilton,2006,CW,5,0,NK,0
Hamilton,2007,CW,4,0,NK,0
Hamilton,2008,NK,8,1,NK,1
Hamilton,2009,NK,2,0,GG,1
Hamilton,2010,NK,9,0,GG,0
Hairspray,2005,GG,3,1,HC,0
Hairspray,2006,GG,7,1,HC,0
Hairspray,2007,HC,4,0,NK,0
Hairspray,2008,HC,5,0,NK,0
Hairspray,2009,NK,2,0,HC,0
Hairspray,2010,NK,1,0,HC,1
In the Heights,2005,HC,8,0,GG,1
In the Heights,2006,HC,9,0,NK,1
In the Heights,2007,GG,4,1,CW,0
In the Heights,2008,NK,1,1,NK,0
In the Heights,2009,CW,5,1,NK,1
In the Heights,2010,NK,3,0,NK,0
Les Miserables,2005,NK,1,0,NK,1
Les Miserables,2006,NK,4,1,CW,0
Les Miserables,2007,NK,5,0,CW,1
Les Miserables,2008,CW,6,1,GG,1
Les Miserables,2009,CW,1,0,CW,0
Les Miserables,2010,GG,3,1,CW,0
;;;; Dataset B: data POTENTIALPEERS;
infile datalines dsd truncover;
input Peer_name:$8. Year:BEST. Class:$2. Peer_sales:BEST. Index_Flag:BEST. Load:$2. FoF_Flag:BEST.;
datalines4;
Rubella,2005,GG,3,1,CW,1
Rubella,2006,NK,7,0,NK,0
Rubella,2007,NK,9,0,NK,0
Rubella,2008,CW,2,1,NK,1
Rubella,2009,CW,5,1,GG,1
Rubella,2010,NK,9,0,GG,0
Smallpox,2005,GG,6,0,HC,1
Smallpox,2006,NK,3,0,HC,0
Smallpox,2007,NK,4,0,NK,0
Smallpox,2008,NK,2,0,NK,0
Smallpox,2009,NK,5,0,HC,0
Smallpox,2010,NK,5,1,HC,1
Mumps,2005,NK,7,1,GG,1
Mumps,2006,CW,2,1,NK,1
Mumps,2007,CW,6,0,CW,0
Mumps,2008,NK,9,0,NK,0
Mumps,2009,GG,7,1,NK,1
Mumps,2010,NK,5,0,NK,0
Measles,2005,NK,3,1,NK,1
Measles,2006,HC,2,0,CW,0
Measles,2007,HC,5,1,CW,0
Measles,2008,HC,6,1,GG,1
Measles,2009,HC,2,0,CW,0
Measles,2010,GG,3,1,CW,0
;;;; I want to output every row from Dataset B, for a given Fund_name in Dataset A that 1) has a "class" value that appears for any row in a fund_name AND 2) has the exact year - index_flag - load - FoF flag The following code lets me do this by each Fund_name: %MACRO findpeers(fundname);
proc sql;
create table atissue.&fundname._peers as
select a.fund_name, b.*
from atissue.atissue a
inner join atissue.potentialpeers b
on a.year = b.year AND
a.index_flag = b.index_flag AND
a.load = b.load AND
a.fof_flag = b.fof_flag
where b.class in (select distinct class from atissue.atissue where a.fund_name = "&fundname");
quit;
%MEND findpeers;
%findpeers (Hamilton);
%findpeers (Hairspray);
%findpeers (In the Heights);
%findpeers (Les Miserables); Questions: 1) The above code seem to work for Hamilton and Hairspray, but why not In the Heights and Les Miserables? Is that because there are spaces? If so, how can I get around that? 2) How can I simplify the last four lines such that, instead of doing %findpeers four times, I can "do over unique values in the fund_name column? 3) Do I have to then stack all these datasets from each fund_name in a separate step? I want to stack them vertically. Thank you!
... View more
