Dynamicly select rows and colomns

[HarryLiu]

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

[ndp · Accepted solution]

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.

[Haikuo]

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;

[ErikLund_Jensen]

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;