topic Re: Collapsing duplicates in SAS Data Management

Collapsing duplicates

Kashvig — Thu, 30 Jan 2020 00:03:13 GMT

I have a dataset that lists my center ID and the organs transplanted at that center ID.

I need to convert the dataset to tell me at each unique center ID how many and which organs are transplanted.

What the data looks like:

Center ID Organ

1 Heart

1 Lung

1 Kidney

2 Heart

3 Heart

3 Liver

4 Heart

4 Lung

5 Liver

6 Lung

7 Heart

7 Lung

7 Liver

7 Kidney

What I need it to look like:

Center ID Organ

1 Heart+Lung+Kidney

2 Heart

3 Heart+Liver

4 Heart+Lung

5 Liver

6 Lung

7 Heart+Lung+Liver+Kidney

The last column can be coded for the different combinations of organs. Any help will be truly appreciated!!

Re: Collapsing duplicates

novinosrin — Thu, 30 Jan 2020 00:38:48 GMT


data have;
input (CenterID      Organ ) ($);
cards;
1                  Heart
1                  Lung
1                  Kidney
2                  Heart
3                  Heart 
3                  Liver
4                  Heart
4                  Lung
5                  Liver
6                  Lung
7                  Heart
7                 Lung
7                 Liver
7                 Kidney
;

data want;
 do until(last.centerid);
  set have;
  by centerid;
  length want $100;
  want=catx('+',want,organ);
 end;
 drop organ;
run;

Re: Collapsing duplicates

plevcek — Thu, 30 Jan 2020 00:48:57 GMT

Here is one option:

data have;
infile datalines;
input centerId 1. organ:$20.;
datalines;
1 Heart
1 Lung
1 Kidney
2 Heart
3 Heart 
3 Liver
4 Heart
4 Lung
5 Liver
6 Lung
7 Heart
7 Lung
7 Liver
7 Kidney
;

proc sort data=have;
by centerid organ;

DATA want;
SET have;
BY centerId organ;
FORMAT organ_list $100. cnt 9.;
RETAIN organ_list cnt;

IF first.centerid THEN
DO;
	organ_list = '';
	cnt = 0;
END;

	organ_list = CATX('+',organ_list,organ);
	cnt+1;

	IF last.centerid; *output only last one for centerID;
DROP organ;
RUN;

Re: Collapsing duplicates

ErikLund_Jensen — Mon, 03 Feb 2020 16:52:42 GMT

Hi @Kashvig

According to your post, you want for each unique center ID how many and which organs are transplanted. Your output example doesn't include counts, but here is an expanded version of the code provided by @novinosrin ,that includes counts for each organ and total.

data have;
input (CenterID      Organ ) ($);
cards;
1                  Heart
1                  Lung
1                  Kidney
1                  Heart
2                  Heart
3                  Heart 
3                  Liver
4                  Heart
4                  Lung
5                  Liver
6                  Lung
7                  Heart
7                 Lung
7                 Lung
7                 Liver
7                 Kidney
;

proc sql;
	create table w as
		select distinct CenterID, Organ, count(*) as Cnt
		from have
		group by CenterID, Organ 
		order by  CenterID, Organ;
quit;

data want;
	drop Cnt Tot organ;

	do until(last.centerid);
		set w;
		length want $100;
		by centerid;
		if first.centerid then Tot = 0;
		want=catx(' + ',want,trim(organ)||':'||strip(put(Cnt,8.)));
		Tot = Tot + Cnt;
	end;
	want=catx(' - ',want,'Total:'||strip(put(Tot,8.)));
run;