topic Re: Identifying duplicate values in SAS Programming

Identifying duplicate values

r3570 — Tue, 26 Oct 2021 12:18:52 GMT

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 ?

Re: Identifying duplicate values

PaigeMiller — Tue, 26 Oct 2021 12:20:45 GMT

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.

Re: Identifying duplicate values

r3570 — Tue, 26 Oct 2021 12:30:29 GMT

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 ??

Re: Identifying duplicate values

PaigeMiller — Tue, 26 Oct 2021 12:39:05 GMT

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.

Re: Identifying duplicate values

r3570 — Tue, 26 Oct 2021 12:40:20 GMT

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

Re: Identifying duplicate values

PaigeMiller — Tue, 26 Oct 2021 13:00:27 GMT

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

Re: Identifying duplicate values

r3570 — Tue, 26 Oct 2021 13:01:39 GMT

sorting can be done by subject

Re: Identifying duplicate values

Quentin — Tue, 26 Oct 2021 13:03:47 GMT

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;

Re: Identifying duplicate values

Kurt_Bremser — Tue, 26 Oct 2021 13:06:14 GMT

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.

Re: Identifying duplicate values

r3570 — Tue, 26 Oct 2021 13:24:44 GMT

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

Re: Identifying duplicate values

r3570 — Tue, 26 Oct 2021 13:27:31 GMT

Apologies..!!

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