topic Re: If statement in SAS Programming

If statement

SalmaTas — Sun, 25 Oct 2015 06:21:08 GMT

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

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?

Re: If statement

kannand — Sun, 25 Oct 2015 06:33:43 GMT

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

Re: If statement

SalmaTas — Sun, 25 Oct 2015 07:36:15 GMT

yes, please find the atatched excel sheet

Re: If statement

Patrick — Sun, 25 Oct 2015 08:51:49 GMT

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;

Re: If statement

SalmaTas — Sun, 25 Oct 2015 09:36:20 GMT

thanks a lot for the great support.

Re: If statement

Kurt_Bremser — Sun, 25 Oct 2015 12:04:30 GMT

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;