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

Abhinav26 — Thu, 29 Apr 2021 15:04:27 GMT

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

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

Kurt_Bremser — Thu, 29 Apr 2021 15:12:15 GMT

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.

topic Re: SAs proc sql multiple rows of data for single entity in a dataset in SAS Programming

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

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