Solved
Contributor
Posts: 23

# Dynamicly select rows and colomns

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.

Harry

Accepted Solutions
Solution
‎10-22-2015 04:37 PM
Contributor
Posts: 61

## Re: Dynamicly select rows and colomns

``````

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.

All Replies
Solution
‎10-22-2015 04:37 PM
Contributor
Posts: 61

## Re: Dynamicly select rows and colomns

``````

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.

Posts: 3,167

## Re: Dynamicly select rows and colomns

[ Edited ]

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;```

Contributor
Posts: 47

## Re: Dynamicly select rows and colomns

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;

🔒 This topic is solved and locked.