Unique values

[Kalai2008]

Hello,

I have a dataset with variables ID and product. I have to get following data .
1. Need to grab ID's separately that have only one color exist (like 2,5,7) in a separate dataset (Doesn't matter on color).
2. Need to create another dataset that only have the combination of both Green and any other color (Example : Id 1, 2,3,4,5,6 ) but should not pick ID 7 & 8.

Id      Color
1       Red
1       Blue
1       Orange
1       Green
2       Green
3       Green
3       Red
3       Orange
4       Purple
4       Green
4       Red
5      Green
6      Green
6      Red
6      Orange
7       Red
7      Red

8      Orange

8     Purple

My current code not working on this.

proc sql;
create table a as
 select *
  from have
   group by id
    having count(distinct color) ne 1;

create table b as
 select *
  from have
   group by id
    having count(distinct color) eq 1;
quit;

data aa;
 set a;
 by id color;
 if first.color;
run;
data want;
 set aa b;
run;

Thanks for checking!

[novinosrin]

data have;
input Id      Color $;
cards;
1       Red
1       Blue
1       Orange
1       Green
2       Green
3       Green
3       Red
3       Orange
4       Purple
4       Green
4       Red
5      Green
6      Green
6      Red
6      Orange
7       Red
7      Red
8      Orange
8     Purple
;

data one_color multi_color;
length _c $10;
call missing(__c);
do until(last.id);
set have;
by id color notsorted;
if _c ne color then __c+1;
_c=color;
end;
do until(last.id);
set have;
by id color notsorted;
if __c=1 then output one_color;
else output multi_color;
end;
drop _:;
run;

[Kalai2008]

Thank you so much.. Your code works awesome picking the distinct colors( one color in separate dataset) and rest as Multi-color in another dataset. Your code provides some great idea. I am trying on the combination part.

[novinosrin]

Just follow the logic and substitute for 2 req. If you are unable to, let us know. 

[novinosrin · Accepted solution]

For Green_plus_comb

data one_color multi_color green_plus_comb;
length _c $10;
call missing(__c,_f);
do until(last.id);
set have;
by id color notsorted;
if _c ne color then __c+1;
_c=color;
if color="Green" then _f=1;
end;
do until(last.id);
set have;
by id color notsorted;
if __c=1 then output one_color;
else output multi_color;
if _f  then output green_plus_comb;
end;
drop _:;
run;

[Kalai2008]

Thank you and it worked 🙂

[Astounding]

To get all the pieces:

data one_color multi_colors green_plus_another;

length go $ 2;

n_color=0;

do until (last.id);

   set have;

   by id color notsorted;

   if last.color then do;

      n_color + 1;

      if color='Green' then substr(go, 1, 1) = 'G';

      else substr(go, 2, 1) = 'O';

   end;

end;

do until (last.id);

   set have;

   by id;

   if go='GO' then output green_plus_another;

   if n_color=1 then output one_color;

   else output multi_colors;  /* optional, if you actually want this piece */

end;

drop GO n_color;

run;

[Kalai2008]

Thank you so much and appreciate your Help. It worked.

[Ksharp]

data have;
input Id      Color $;
cards;
1       Red
1       Blue
1       Orange
1       Green
2       Green
3       Green
3       Red
3       Orange
4       Purple
4       Green
4       Red
5      Green
6      Green
6      Red
6      Orange
7       Red
7      Red
8      Orange
8     Purple
;
proc sql;
select *
 from have
  group by id
   having sum(color='Green') ne 0 ;
 quit;