topic Re: Unique values in SAS Programming

Unique values

Kalai2008 — Mon, 15 Oct 2018 16:14:16 GMT

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!

Re: Unique values

novinosrin — Mon, 15 Oct 2018 16:31:10 GMT

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;

Re: Unique values

Kalai2008 — Mon, 15 Oct 2018 16:53:24 GMT

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.

Re: Unique values

novinosrin — Mon, 15 Oct 2018 17:01:24 GMT

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

Re: Unique values

novinosrin — Mon, 15 Oct 2018 17:31:29 GMT

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;

Re: Unique values

Astounding — Mon, 15 Oct 2018 17:37:15 GMT

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;

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;

Re: Unique values

Kalai2008 — Mon, 15 Oct 2018 17:54:33 GMT

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

Re: Unique values

Kalai2008 — Mon, 15 Oct 2018 17:55:10 GMT

Thank you and it worked 🙂

Re: Unique values

Ksharp — Tue, 16 Oct 2018 13:48:29 GMT

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;