Group diagnosis code into disease classes

[tan-wongv]

I successfully grouped the diagnosis code into disease classes and label it as "yes" for each DMRN. This is how the outcome looks like:

DMRN	DX_CODE	DX_NAME	nervous	circular	respiratory	genitourinary	constipation	DM
7	I10	hypertension		Yes
7	J44.9	COPD			Yes
7	M19.90	Arthritis
7	N40.0	BPH				Yes

Then, I would like to collapse the rows so that each DMRN has only one row and drop DX_CODE and DX_NAME.
This is how I want the table look like:

DMRN	nervous	circular	respiratory	genitourinary	constipation	DM
7		Yes	Yes	Yes

What codes should I use? Thank you so much for your help.

[FreelanceReinh · Accepted solution]

Hello @tan-wongv,

Make sure that your dataset ("HAVE") is sorted by DMRN and then use a DATA step with an UPDATE statement:

data want;
update have(drop=dx: obs=0) have(drop=dx:);
by dmrn;
run;

This code drops variables whose names start with "DX" and creates one observation for each DMRN, containing the last non-missing value (if any) of NERVOUS, CIRCULAR, etc.

[tan-wongv]

Thank you so much for your help! I appreciate your explanation 😊