## If statement

Solved
Occasional Contributor
Posts: 8

# 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
Posts: 4,737

## 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;``````

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
Posts: 4,737

## 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: 10,280

## 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
How to convert datasets to data steps
How to post code
🔒 This topic is solved and locked.