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
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.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.