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!
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;
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;
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.
Just follow the logic and substitute for 2 req. If you are unable to, let us know.
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;
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;
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;
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.