here is my solution.
data a;
input ID y1 $2. y2 $2. y3 $2.;
cards;
1 A V E
2 G X
3 A C R
run;
%macro trans_data;
proc sql noprint;
select y into: value1- from
(select distinct y1 as y from a where y1 is not null
union select distinct y2 as y from a where y2 is not null
union select distinct y3 as y from a w ...
data a;
input ID y1 $2. y2 $2. y3 $2.;
cards;
1 A V E
2 G X
3 A C R run;
%macrotrans_data;
proc sql noprint;
select y into: value1- from
(select distinct y1 as y from a where y1 is not null
union select distinct y2 as y from a where y2 is not null
union select distinct y3 as y from a where y3 is not null)
;
quit;
data a_new;
set a;
array yvar y1-y3;
%do i=1 %to &sqlobs.;
&&value&i=0;
%end;
do over yvar;
select(yvar);
%do i=1 %to &sqlobs.;
when("&&value&i") &&value&i=1;
%end;
otherwise;
end;
end;
drop y1-y3;
run; %mend;
%trans_data;
It works, but is not efficient for a big data. data a;
input ID y1 $2. y2 $2. y3 $2. ;
cards;
1 A V E
2 G X
3 A C R run; data b (keep=ID A V E G X C R);
set a;
array aa{*} A V E G X C R;
array bb{*} y1-y3;
do i=1 to dim(aa);
aa=0;
do j=1 to dim(bb);
if bb[j]=vname(aa) then do;
aa=1;
leave ;
end;
end;
end; run;