If statement

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 8
Accepted Solution

If statement

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? 


Accepted Solutions
Solution
‎10-25-2015 05:35 AM
Respected Advisor
Posts: 4,173

Re: If statement

[ Edited ]

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;

 

View solution in original post


All Replies
Regular Contributor
Posts: 161

Re: If statement

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

Kannan Deivasigamani
Occasional Contributor
Posts: 8

Re: If statement

yes, please find the atatched excel sheet 

Solution
‎10-25-2015 05:35 AM
Respected Advisor
Posts: 4,173

Re: If statement

[ Edited ]

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;

 

Occasional Contributor
Posts: 8

Re: If statement

thanks a lot for the great support.
Super User
Posts: 7,809

Re: If statement

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;
  
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 605 views
  • 2 likes
  • 4 in conversation