topic Re: How to identify duplicate value for one column in mlultiple group in SAS Programming

How to identify duplicate value for one column in mlultiple group

Spintu — Wed, 27 May 2020 06:58:49 GMT

Here is the My Sample code. I want to identify in X1 column group for 1 the X3 column values are 10,11 and 13.

Similarly when I will check for the other group in X1 column for group 2 if the X3 values are repeated for group 1 values then I have to replace with null. Could you please assist me.

data temp;
input x1 x2 x3 x4$ x5$;
cards;
1 2 10 AA BB
1 2 11 AA BB
1 2 13 AA BC
2 3 14 AB CD
2 3 11 AB CE
3 4 15 AC DF
4 5 16 AD EG
4 5 10 AD EH
5 6 11 AE FI
6 7 18 AF GJ
6 7 13 AF GK

;
run;

Expecting output should be :
1  2 10       AA BB
1  2 11      AA BB
1  2 13      AA BC
2  3 14       AB CD
2  3 NULL AB CE
3  4 15      AC DF
4  5 16      AD EG
4  5 NULL AD EH
5  6 NULL AE FI
6  7 18       AF GJ
6  7 NULL AF GK

Re: How to identify duplicate value for one column in mlultiple group

Jagadishkatam — Wed, 27 May 2020 07:10:10 GMT

Please try the below code


proc sql noprint;
select x3 into: vals separated by ',' from temp where x1=1;
quit;

%put &vals;

data want;
set temp;
if x1 ne 1 and x3 in (&vals) then x3=.;
run;

Re: How to identify duplicate value for one column in mlultiple group

PeterClemmensen — Wed, 27 May 2020 07:15:46 GMT

The data step does not have a null value. Do you want it to be zero or missing?

And what if the same group contains a duplicate like this?

1 2 10 AA BB
1 2 11 AA BB
1 2 10 AA BC

Re: How to identify duplicate value for one column in mlultiple group

PeterClemmensen — Wed, 27 May 2020 07:23:26 GMT

My 2 cents

data want;
    declare hash h ();
    h.definekey ("x3");
    h.definedone();

    do until (lr);
        set temp end=lr;
        if h.add() ne 0 then x3=.;
        output;
    end;
run;

Result

Obs x1 x2 x3  x4  x5 
1   1  2  10  AA  BB 
2   1  2  11  AA  BB 
3   1  2  13  AA  BC 
4   2  3  14  AB  CD 
5   2  3  .   AB  CE 
6   3  4  15  AC  DF 
7   4  5  16  AD  EG 
8   4  5  .   AD  EH 
9   5  6  .   AE  FI 
10  6  7  18  AF  GJ 
11  6  7  .   AF  GK

Re: How to identify duplicate value for one column in mlultiple group

Spintu — Wed, 27 May 2020 07:56:05 GMT

anything is fine zero or missing.

Re: How to identify duplicate value for one column in mlultiple group

PeterClemmensen — Wed, 27 May 2020 07:57:01 GMT

And what if a single group contains duplicates?

Re: How to identify duplicate value for one column in mlultiple group

Spintu — Wed, 27 May 2020 10:16:10 GMT

Thank you so much. this what exactly what I am looking for. I accepted.