Hope this was for a 'real' situation and not only to do someones homework...
HTH
Patrick
data doctor;
input specialtykey doctorname $30.;
datalines;
1 john
2 Sam
3 Mary
;
run;
data specialty;
input specialtykey specialty $30.;
datalines;
1 ENT
1 Cardiologist
1 General
2 eyes
2 family
3 Gastro
;
run;
proc sql;
create view DoctorSpecialty as
select d.*,s.specialty
from doctor as d left join specialty as s
on d.specialtykey=s.specialtykey
order by doctorname
;
quit;
proc transpose data=DoctorSpecialty out=want(drop=_name_) prefix=Specialty;
by doctorname;
var specialty;
run;
Looking at the two tables the data model looks kind of weird to me. I would expect the key to be "doctorkey" and this key to be the foreign key in specialty.
If this would be normalised then I would expect to see the following tables:
Doctor {doctorkey, name}
Specialty {specialtykey,specialty}
DoctorSpecialty {doctorkey,specialtykey}
