combining multiple datasets using proc sql;

[tgleghorn]

This is what I am currently doing.  Everytime there is another abstractor data set I have to add to the statement.  I want to be able to run the code with out having to manually add.

proc sql;
create table sasfile.rh_interface_byabst_&filedate. as
select a.population,
a.shtmeasure,
a.service,
a.caregaps_total,
a.overreads_total,
b.g1,
b.o1,
c.g2,
c.o2,
d.g3,
d.o3,
e.g4,
e.o4,
f.g5,
f.o5,
g.g6,
g.o6,
h.g7,
h.o7,
i.g8,
i.o8,
j.g9,
j.o9,
k.g10,
k.o10,
l.g11,
l.o11
from sasfile.rh_interface_bymeasure_&filedate. as a
left join abstractor1 as b
on a.population=b.population and
a.shtmeasure=b.shtmeasure and
a.service=b.service
left join abstractor2 as c
on a.population=c.population and
a.shtmeasure=c.shtmeasure and
a.service=c.service
left join abstractor3 as d
on a.population=d.population and
a.shtmeasure=d.shtmeasure and
a.service=d.service
left join abstractor4 as e
on a.population=e.population and
a.shtmeasure=e.shtmeasure and
a.service=e.service
left join abstractor5 as f
on a.population=f.population and
a.shtmeasure=f.shtmeasure and
a.service=f.service
left join abstractor6 as g
on a.population=g.population and
a.shtmeasure=g.shtmeasure and
a.service=g.service
left join abstractor7 as h
on a.population=h.population and
a.shtmeasure=h.shtmeasure and
a.service=h.service
left join abstractor8 as i
on a.population=i.population and
a.shtmeasure=i.shtmeasure and
a.service=i.service
left join abstractor9 as j
on a.population=j.population and
a.shtmeasure=j.shtmeasure and
a.service=j.service
left join abstractor10 as k
on a.population=k.population and
a.shtmeasure=k.shtmeasure and
a.service=k.service
left join abstractor11 as l
on a.population=l.population and
a.shtmeasure=l.shtmeasure and
a.service=l.service
;quit;

[PaigeMiller · Accepted solution]

data want;
    merge abstractor:;
    by population shtmeasure service;
run;

This assumes all data sets are sorted by POPULATION SHTMEASURE SERVICE 

[tgleghorn]

wow... that is easy!!  Thank you!