Identifying duplicate values

[r3570]

Dear All,

I have a dataset as below:

SUBJECT        OUTCOME

101                    FATAL

101                    RECOVERED

101                    FATAL

102                    NOT RECOVERED

102                    FATAL

103                    FATAL

103                    UNKNOWN

In the above dataset the subject (101) has outcome as fatal twice which is wrong as per our concept and other subjects (102 and 103) have OUTCOME as fatal only once as per concept. I have more than 100 records with different subject numbers. My requirement is to identify such subjects which have fatal more than once and flag them.

Any help ?

[PaigeMiller]

proc freq data=have;
    tables subject*outcome/noprint out=want;
run;

This identifies the places where there are duplicates, because the value of variable COUNT in data set WANT will be > 1.

[r3570]

Dear Paigemiller,

Thanks for your swift response.

I do not need to count the duplicates but I just need to create a new column and flag the specific value like 'FATAL' as Yes or something so that it will be helpful for our analysis.. Could you please help how to write a prog for that ??

[PaigeMiller]

If you know which combinations have COUNT > 1 then you can use this to "flag" records (although I'm not really sure what you mean by that). Please show me your desired output.

[r3570]

My desired output should be as below:

SUBJECT        OUTCOME                     RESULT

101                    FATAL

101                    RECOVERED

101                    FATAL                             Yes

102                    NOT RECOVERED

102                    FATAL

103                    FATAL

103                    UNKNOWN

[PaigeMiller]

Does the final data set have to be in the order shown, or can we sort it?

[r3570]

sorting can be done by subject

[Quentin]

Are you familiar with by-group processing?  I would try something like (untested):

proc sort data=have;
  by subject outcome;
run;

data want;
  set have;
  by subject outcome;

  if first.outcome=0 and outcome="FATAL" then Result="Yes";
run;

[Kurt_Bremser · Accepted solution]

data want;
set have;
by subject,
retain _fatal;
if first.subject then _fatal = 0;
if outcome = "FATAL"
then do;
  if _fatal then result = "Yes";
  _fatal = 1;
end;
drop _fatal;
run;

Untested, posted from my tablet.

[r3570]

Output is not as expected. I cannot see the result variable populated as 'Yes' though there is repeated value in outcome for same subject

[r3570]

Apologies..!!

out put is as expected. there was issue in case sensitive info...i rectified it.. thank you so much..!!