SAs proc sql multiple rows of data for single entity in a dataset

[Abhinav26]

Hi,

Please help me here.

Original Data:

Personal_id Type_Medication

111              Diabetes

222             Diabetes

333             Maternity

444             Maternity

I have separate dataset and I want to add this data to original dataset in the manner listed below.

Separate dataset - 

Type_Medication      Text

Maternity                  Parental care needed
Maternity                  Doctor needed
Diabetes                   Nurse needed
Diabetes                   Nurse/Doctor both needed
Diabetes                   Hospitalization required

Output required as below:

Personal_id Type_Medication Type

111             Diabetes              Nurse needed

111             Diabetes              Nurse/Doctor both needed

111             Diabetes              Hospitalization required

222            Diabetes              Nurse needed

222            Diabetes              Nurse/Doctor both needed

222            Diabetes              Hospitalization required

333            Maternity             Parental care needed

333            Maternity             Doctor needed

444            Maternity             Parental care needed

444            Maternity             Doctor needed

[Kurt_Bremser · Accepted solution]

Both a DATA step merge and a SQL join will handle this one-to-many relationship:

proc sort data=orig;
by type_medication;
run;

proc sort data=sep;
by type_medication;
run;

data want;
merge
  orig (in=o)
  sep
;
by type_medication;
if o;
run;

/* if needed */
proc sort data=want;
by personal_id;
run;

proc sql;
create table want as
  select
    o.personal_id,
    o.type_medication,
    s.text as type
  from orig o
  left join sep s
  on o.type_medication = s.type_medication
  order by o.personal_id
;
quit;

You can also contemplate using a hash and hash iterator object for the separate table, so no explicit sorting has to be done.