topic Re: How to remove duplicates and replace data by specfic condition in SAS Programming

How to remove duplicates and replace data by specfic condition

ttqkroe — Fri, 22 Oct 2021 03:00:38 GMT

Hi, I have a question about how to remove duplicates and also replace data by specfic condition.

I have built a table below, I want for all the variables a to h, if on the same ID, the variable has a 'yes' then the variable will show 'yes' and if the variable only has 'no' then the varable will show 'no'.

ID	a	b	c	d	e	f	g	h
1	no	no	no	no	no	yes	no	no
1	no	yes	no	no	no	no	no	no
1	yes	no	no	yes	no	no	no	no
2	no	no	yes	no	no	no	yes	no
2	yes	no	no	no	no	yes	no	no

The result should look like:

ID	a	b	c	d	e	f	g	h
1	yes	yes	no	yes	no	yes	no	no
2	yes	no	yes	no	no	yes	yes	no

Re: How to remove duplicates and replace data by specfic condition

PeterClemmensen — Fri, 22 Oct 2021 05:44:24 GMT

data have;
input ID (a b c d e f g h)(:$3.);
datalines;
1 no  no  no  no  no  yes no  no
1 no  yes no  no  no  no  no  no
1 yes no  no  yes no  no  no  no
2 no  no  yes no  no  no  yes no
2 yes no  no  no  no  yes no  no
;

data want;
   do until (last.ID);
      set have;
      by ID;
      array r $ a -- h;
      array rr {8} _temporary_;
      
      do over r;
         if r = "yes" then rr[_i_] = 1;
      end;
   end;
   
   do over r;
      r = ifc(rr[_i_], "yes", "no");
   end;

   call missing (of rr[*]);
run;

Result:

ID a   b   c   d   e   f   g   h
1  yes yes no  yes no  yes no  no
2  yes no  yes no  no  yes yes no

Re: How to remove duplicates and replace data by specfic condition

Shmuel — Fri, 22 Oct 2021 05:58:36 GMT

You can use next tested code:

data have;
input ID (a b c d e f g h)(:$3.);
datalines;
1 no  no  no  no  no  yes no  no
1 no  yes no  no  no  no  no  no
1 yes no  no  yes no  no  no  no
2 no  no  yes no  no  no  yes no
2 yes no  no  no  no  yes no  no
;
run;

proc sql;
  select max(a) as a,
         max(b) as b,
         max(c) as c,
         max(d) as d,
         max(e) as e,
         max(f) as f,
         max(g) as g,
         max(h) as h
  from have 
  group by ID;
quit;

ID	a	b	c	d	e	f	g	h
1	no	no	no	no	no	yes	no	no
1	no	yes	no	no	no	no	no	no
1	yes	no	no	yes	no	no	no	no
2	no	no	yes	no	no	no	yes	no
2	yes	no	no	no	no	yes	no	no

ID	a	b	c	d	e	f	g	h
1	no	no	no	no	no	yes	no	no
1	no	yes	no	no	no	no	no	no
1	yes	no	no	yes	no	no	no	no
2	no	no	yes	no	no	no	yes	no
2	yes	no	no	no	no	yes	no	no

ID	a	b	c	d	e	f	g	h
1	no	no	no	no	no	yes	no	no
1	no	yes	no	no	no	no	no	no
1	yes	no	no	yes	no	no	no	no
2	no	no	yes	no	no	no	yes	no
2	yes	no	no	no	no	yes	no	no