How to identify if values are same in 2 different variables

[r3570]

Dear All,

I have a dataset as below:

ID         DATE            EVENT

101    20Apr2021     Headache

101    21Apr2021     Nausea

101    20Apr2021     Headache

102    01May2021    Vomiting

102    02May2021    Vomiting

103    01Aug2021    Fatigue

104    01Aug2021    Fatigue

I need to flag the records where the observations in date and event column are same per each ID.

The output should be like the below:

ID         DATE            EVENT         FLAG

101    20Apr2021     Headache         Y

101    21Apr2021     Nausea      

101    20Apr2021     Headache         Y

102    01May2021    Vomiting

102    02May2021    Vomiting

103    01Aug2021    Fatigue

104    01Aug2021    Fatigue

Please help

[PeterClemmensen · Accepted solution]

One way

data have;
input ID DATE :date9. EVENT $;
format DATE date9.;
datalines;
101 20Apr2021 Headache
101 21Apr2021 Nausea
101 20Apr2021 Headache
102 01May2021 Vomiting
102 02May2021 Vomiting
103 01Aug2021 Fatigue
104 01Aug2021 Fatigue
;

data want(drop = c);
   if _N_ = 1 then do;
      dcl hash h();
      h.definekey("ID", "DATE", "EVENT");
      h.definedata("c");
      h.definedone();
      
      do until (z);
         set have end = z;
         if h.find() ne 0 then c = 1;
         else                  c + 1;
         h.replace();
      end;
   end;
   
   set have;
   
   if h.find() = 0 and c > 1 then flag = "Y";
run;

Result:

Obs  ID   DATE       EVENT     flag
1    101  20APR2021  Headache  Y
2    101  21APR2021  Nausea  
3    101  20APR2021  Headache  Y
4    102  01MAY2021  Vomiting  
5    102  02MAY2021  Vomiting  
6    103  01AUG2021  Fatigue  
7    104  01AUG2021  Fatigue  

[r3570]

This looks somewhat complex program. Hard for me to understand. Could you please help me with a simple easy to understand program ?

[FreelanceReinh]

Hello @r3570,

---

@r3570 wrote:

This looks somewhat complex program. Hard for me to understand. Could you please help me with a simple easy to understand program ?

---

Maybe it's easier to understand if you split the task into several short steps:

/* Create a temporary unique sort key _SEQNO */

data _tmp / view=_tmp;
set have;
_seqno=_n_;
run;

/* Separate duplicate and unique ID-DATE-EVENT combinations */

proc sort data=_tmp out=dup nouniquekey uniqueout=uni;
by id date event;
run;

proc sort data=dup; by _seqno; run;
proc sort data=uni; by _seqno; run;

/* Reunite the two parts, flagging the duplicates */

data want(drop=_seqno);
set dup(in=d) uni;
by _seqno;
if d then flag='Y';
run;