Adding new columns

[ren2010]

Hi,

I have a situation like the following

example:

doctor
-----------------------------------
specialtykey doctorname
1 john
2 Sam
3 Mary



doctor specialty table
----------------------------
specialtykey specialty
1 ENT
1 Cardiologist
1 General
2 eyes
2 family
3 Gastro

My output dataset should look like this

Final
----------
doctorname specialty1 specialty2 specialty3
john ENT Cardiologist General
sam eyes family
mary Gastro



Please help

[Patrick]

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}

[ren2010]

Thanks So Much.