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;
data want;
merge abstractor:;
by population shtmeasure service;
run;
This assumes all data sets are sorted by POPULATION SHTMEASURE SERVICE
data want;
merge abstractor:;
by population shtmeasure service;
run;
This assumes all data sets are sorted by POPULATION SHTMEASURE SERVICE
wow... that is easy!! Thank you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.