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?
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;
The actual Excel sheet may help. Is it possible to attach here ?
yes, please find the atatched excel sheet
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.