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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.