If statement

[SalmaTas]

there is a nested IF Statement i apply in excel, and i want to know how I do it in SAS Programming, i am sure it is doable, i am just not sure how to do it. 

ex: 

i have a table containing the below data

ID                     Color 

123                   Blue

123                   Yellow

123                   Green

345                   Pink

345                   Red

345                   Blue

345                   White

the result i want is: 

ID                     Color 

123                   Blue, Yellow, Green

123                   Blue, Yellow, Green

123                   Blue, Yellow, Green

345                   Pink, Red, Blue, White

345                   Pink, Red, Blue, White

345                   Pink, Red, Blue, White

345                   Pink, Red, Blue, White

the IF Statements i use for the first option (where there is 3 duplicated ID number, for the 9th row) is: 

=IF((A9=A8)*AND(A9=A7),G7&"," G8&","&G9,(IF((A9=A8)*AND(A9=A10),G8&"," &G9&","G10,(IF((A9=A10)*AND(A9=A11),G9&G10&G11))))

the IF Statements i use for the second option (where there is 4 duplicated ID number, for the 16th row) is: 

=IF((A16=A15)*AND(A16=A14)*AND(A13=A16),G13&","&G14&","&G15&","&G16,(IF((A16=A14)*AND(A16=A15)*AND(A16=A17),G14&","&G15&","&G16&","&G17,(IF((A16=A15)*AND(A16=A17)*AND(A16=A18),G15&","&G16&","&G17&","&G18,(IF((A16=A17)*AND(A16=A18)*AND(A16=A19),G16&","&G17&","&G18&","&G19)))))))

how do i get the same result using SAS Programming? 

[kannand]

The actual Excel sheet may help. Is it possible to attach here ?

[SalmaTas]

yes, please find the atatched excel sheet 

[Patrick · Accepted solution]

One way to go:

data have;
 infile datalines truncover;
 input ID Color $;
datalines; 
123 Blue
123 Yellow
123 Green
345 Pink
345 Red
345 Blue
345 White
;
run;

proc transpose data=have out=inter prefix=_color;
  by id;
  var color;
run;

data want(drop=_:);
  merge have(keep=id) inter;
  by id;
  length color $255;
  color=catx(',',of _color:);
run;

or this way:

data want(drop=_:);
  set have(keep=id);
  by id;
  length color $255;
  retain color;
  if first.id then
    do;
      call missing(color);
      do until(last._id2);
        set have(rename=(id=_id2 color=_color2));
        by _id2;
        color=catx(',',color,_color2);
      end;
    end;
run;

or this way:

data inter(drop=_:);
  set have(rename=(color=_color));
  by id;
  length color $255;
  retain color;
  color=catx(',',color,_color);
  if last.id then
    do;
      output;
      call missing(color);
    end;
run;

proc sql;
  create table want as
    select r.id, r.color
    from have l, inter r
    where l.id=r.id
  ;
quit;

[SalmaTas]

thanks a lot for the great support.

[Kurt_Bremser]

Still another method:

data want (drop=oldcolor count index);
set have (rename=(color=oldcolor));
by id;
length color $ 200;
if first.id
then do;
  count = 0;
  color = '';
end;
color = catx(',',trim(color),oldcolor);
count + 1;
if last.id
then do;
  do index = 1 to count;
    output;
  end;
end;
run;