Hi SASkiwi, I drew on your insights, and added some other codes to finally create the result i desired, however, i have to ran several times to arrive at the final result, please see my codes below: data have;
informat account_id $8. m1-m10 $1.;
input Account_ID $ M1-M10 $;
cards;
A00001 C M M M M M M M M P
A00002 C P C M M M M M M M
A00003 P P M C M M M M M M
;
run;
data want;
set have;
array m(10) m1-m10;
array p(7) p1-p7 ;
do I = 1 to 7;
text=catt(m(i), m(i+1), m(i+2), m(i+3));
if index(text, 'CMMM') then p(i) = 3;
else p(i)=0;
end;
drop i text;
run;
data want1;
set want;
array m(10) m1-m10;
array p(6) p8-p13 ;
do I= 1 to 6;
text=catt(m(i), m(i+1), m(i+2), m(i+3), m(i+4));
if index(text, 'CMMMM') then p(i) = 4;
else p(i)=0;
end;
drop i text;
run;
data want2;
set want1;
array m(10) m1-m10;
array p(5) p14-p18 ;
do I= 1 to 5;
text=catt(m(i), m(i+1), m(i+2), m(i+3), m(i+4), m(i+5));
if index(text, 'CMMMMM') then p(i) = 5;
else p(i)=0;
end;
drop i text;
run;
Data want3;
set want2;
array m(10) m1-m10;
array p(4) p19-p22 ;
do I= 1 to 4;
text=catt(m(i), m(i+1), m(i+2), m(i+3), m(i+4), m(i+5),m(i+6));
if index(text, 'CMMMMMM') then p(i) = 6;
else p(i)=0;
end;
drop i text;
run;
Data want4;
set want3;
array m(10) m1-m10;
array p(3) p23-p25 ;
do I= 1 to 3;
text=catt(m(i), m(i+1), m(i+2), m(i+3), m(i+4), m(i+5),m(i+6),m(i+7));
if index(text, 'CMMMMMMM') then p(i) = 7;
else p(i)=0;
end;
drop i text;
run;
Data want_final;
set want4;
array m(10) m1-m10;
array p(2) p26-p27 ;
do I= 1 to 2;
text=catt(m(i), m(i+1), m(i+2), m(i+3), m(i+4), m(i+5),m(i+6),m(i+7),m(1+8));
if index(text, 'CMMMMMMMM') then p(i) = 8;
else p(i)=0;
end;
drop i text;
Max=max(of p1-p27);
Min=min(of p1-p27);
run; I am wondering if there is a "magic wand" that you can help me wave that turns them into a series of coherent steps to produce the final result with one fell swoop.Thanks!
... View more