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 |
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;
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
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.