topic Flag items with same unique ID but different categories for a variable in SAS Procedures

Flag items with same unique ID but different categories for a variable

jcapua2 — Tue, 02 Jan 2018 19:47:59 GMT

Hello,

data have;

patient_num	Procedure Date	Status Date	Organ
patient11	03/07/2002	02/23/2004	Heart
patient11	02/23/2004	10/01/2014	Lung
patient12	04/23/2006	10/13/2014	Heart
patient13	10/21/2007	10/10/2016	Bone
patient14	05/06/2009	04/30/2017	Liver
patient14	05/09/2010	07/15/2017	Liver

data want;

patient_num	Procedure Date	Status Date	Organ	Multiorgan
patient11	03/07/2002	02/23/2004	Heart	Yes
patient11	02/23/2004	10/01/2014	Lung	Yes
patient12	04/23/2006	10/13/2014	Heart	No
patient13	10/21/2007	10/10/2016	Bone	No
patient14	05/06/2009	04/30/2017	Liver	No
patient14	05/09/2010	07/15/2017	Liver	No

I want to flag rows where a patient has multiple organ procedures. So for patient 11, he/she has both heart and lung surgeries. So new variable Multiorgan='Yes'.

Is there a way to do this in data step? Or proc SQL?

Thank you!

Re: Flag items with same unique ID but different categories for a variable

Reeza — Tue, 02 Jan 2018 20:01:45 GMT

SQL is quicker in this case.

proc sql;
create table want as
select *, 
      case when max(organ)=min(organ) then 'No'
                   else 'Yes' end 
      as multiorgan
from have
group by patient_num;
quit;

Re: Flag items with same unique ID but different categories for a variable

mkeintz — Tue, 02 Jan 2018 22:25:43 GMT

If the data are sorted by patient_num:

data want;

set have;

by patient_num;

if first.patient_num and last.patient_num then multi_organ='No';

else multi_organ='Yes';

run;