There was a similar topic recently.
https://communities.sas.com/t5/SAS-Programming/Combining-Data-Vertically/td-p/696072
data have1;
infile datalines dlm="09"x;
input Site:$11. PatientID FirstNm $ LastNm $ Sex $;
datalines;
St Johns 203 Daniel Taylor M
St Johns 206 Helen Davis F
St Johns 208 Betty Smith f
;
run;
data have2;
infile datalines dlm="09"x;
input Institution:$11. SubjID Name:$15. Gender $;
datalines;
City Medics 102 Wilson, Steven Male
City Medics 105 Moore, Chris male
City Medics 109 Jackson, Sharon Female
;
run;
DATA want;
if _N_=1 then
do;
RETAIN Institution;
LENGTH Institution $11. SubjID 8 Name $15. sexcd $10. ;
declare hash h(multidata:"y",ordered:"y");
h.definekey("Institution");
h.definedata("Institution","SubjID","name","sexcd");
h.definedone();
call missing(subjid);
declare hiter hi("h");
end;
do until(last1);
SET WORK.have1 (RENAME=(Site=Institution PatientID=SubjID sex=sexcd)) end=last1;
Name=FirstNm||","||LastNm;
sexcd=upcase(sexcd);
h.add();
end;
do until(last2);
set have2(RENAME=(Gender=SexCd)) end=last2;
if upcase(sexcd)="MALE" then sexcd="M";
else sexcd="F";
h.add();
end;
do while(hi.next()=0);
output;
end;
drop firstnm lastnm;
RUN;
*proc sql;
proc sql;
create table want as
select a.institution,
a.subjid,
cats(a.firstnm,",",a.lastnm) as name,
upcase(a.sexcd)
from WORK.have1 (RENAME=(Site=Institution PatientID=SubjID sex=sexcd)) a
union
select b.institution,
b.subjid,
b.name,
case upcase(b.gender) when "MALE" then "M" else "F" end as sexcd
from work.have2 b
order by 1;
quit;
... View more