Dear SAS users,
I have a data set that has more than 100 columns and over 10000 rows. I want select rows that have the same value among three consecqtive columns.
For example I have the following table
Obs | st1 | st2 | st3 | st4 | st5 | st6 | st7 |
---|---|---|---|---|---|---|---|
1 | 1 | 2 | 4 | 4 | 5 | 6 | 6 |
2 | 8 | 9 | 10 | 10 | 10 | 13 | 14 |
3 | 15 | 16 | 17 | 17 | 19 | 21 | 21 |
4 | 22 | 23 | 25 | 24 | 24 | 24 | 26 |
5 | 40 | 41 | 42 | 42 | 42 | 43 | 46 |
My result should be like this
obs st_numb
2 st3 st4 st5
4 st4 st5 st6
5 st3 st4 st5
I think combining do loop and array is a potential method, but I don't know how to write code.
Thanks in advance for help!
Harry
data test;
input Obs st1 st2 st3 st4 st5 st6 st7;
datalines;
1 1 2 4 4 5 6 6
2 8 9 10 10 10 13 14
3 15 16 17 17 19 21 21
4 22 23 25 24 24 24 26
5 40 41 42 42 42 43 46
;
run;
data test1;
set test;
array test{*} st1-st7;
do i=1 to (dim(test)-2);
if test{i}=test{i+1}=test{i+2} then x=i;
end;
if x>.;
keep obs x;
run;
data test2;
set test1;
length var $100;
var="";
do i=x to x+2;
var=strip(var)||" st"||strip(put(i,best.));
end;
keep obs var;
run;
Above code will work if you need to collect only single/last instance of triplicate results. You can obviously expand to include more variables but they have to be named in sequence.
data test;
input Obs st1 st2 st3 st4 st5 st6 st7;
datalines;
1 1 2 4 4 5 6 6
2 8 9 10 10 10 13 14
3 15 16 17 17 19 21 21
4 22 23 25 24 24 24 26
5 40 41 42 42 42 43 46
;
run;
data test1;
set test;
array test{*} st1-st7;
do i=1 to (dim(test)-2);
if test{i}=test{i+1}=test{i+2} then x=i;
end;
if x>.;
keep obs x;
run;
data test2;
set test1;
length var $100;
var="";
do i=x to x+2;
var=strip(var)||" st"||strip(put(i,best.));
end;
keep obs var;
run;
Above code will work if you need to collect only single/last instance of triplicate results. You can obviously expand to include more variables but they have to be named in sequence.
Can be done in 1X pass. Please note, the following code ONLY applied to exact consecutive 3 as you asked, not more than 3. But it can be easily tweaked to adapt that.
data want; set test; array st(*) st:; length st_numb $ 100; do _i=1 to dim(st); if _t=st(_i) then _ct+1; else do; if _ct =3 then leave; else do; _t=st(_i); _ct=1; end; end; end; if _ct=3; _ct=1; st_numb=catx(' ', vname(st(_i-3)), vname(st(_i-2)), vname(st(_i-1))); keep obs st_numb; run;
Almost the same as the previous answer, but this vill list the first 3 varables, if 3 or more consecutive variables have the same value.
If this happens more than once in the same row, every set of first-three is output with the same value og obs. If the leave statement is uncommented, only the first set is output.
Obs is the input row number, not a variable from the input data set. To get the value from a variable in the input data set, just omit obs= _N_.
data have;
input (st1-st7)(3.);
cards;
1 2 4 4 5 6 6
8 9 10 10 10 13 14
15 16 17 17 19 21 21
22 23 25 24 24 24 26
40 41 42 42 42 43 46
10 10 10 12 11 11 11
;
data want (keep=obs st_numb);
length obs 8 st_numb $100;
set have;
array a _numeric_;
do i = 2 to dim(a);
if a{i} = a{i-1} then equals + 1;
else equals = 0;
if equals = 2 then do;
obs= _N_; st_numb = catx(' ',vname(a{i-2}), vname(a{i-1}), vname(a{i}));
output;
*leave;
end;
end;
run;
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.
Ready to level-up your skills? Choose your own adventure.