BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
HarryLiu
Obsidian | Level 7

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

 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
ndp
Quartz | Level 8 ndp
Quartz | Level 8


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.

View solution in original post

3 REPLIES 3
ndp
Quartz | Level 8 ndp
Quartz | Level 8


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
Onyx | Level 15

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
Rhodochrosite | Level 12

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: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 1142 views
  • 1 like
  • 4 in conversation