Get consecutive repeating values from a table

[OLUGBOJI]

Please see below for values of what I have vs what I want. I want to get repeated consecutive names

for certain id's and their count and the cummulative count. Proccsql and sql if possible. Thanks

Name	ID	group id
QSAVE	4097	3
OPEN	4097	3
OPEN	4097	3
QSAVE	4097	3
OPEN	4097	3
PASTECLIP	8193	3
MTEDIT	4097	3
GRIP_STRETCH	8193	3
CHPROP	8193	3
CHPROP	8193	3
MATCHPROP	8193	3
COPY	8193	3
TEXTEDIT	8193	3
TEXTEDIT	8193	3
MOVE	8193	3
GRIP_STRETCH	8193	3
MOVE	8193	3
ZOOM	8193	3
QSAVE	4097	3
OPEN	4097	3
PASTECLIP	8193	3
CHPROP	8193	3
CHPROP	8193	3
CHPROP	8193	3
CHPROP	8193	3
CHPROP	8193	3
CHPROP	8193	3
CHPROP	8193	3
CHPROP	8193	3
CHPROP	8193	3
MATCHPROP	8193	3
MATCHPROP	8193	3
CUTCLIP	4097	3
PASTEBLOCK	8193	3
CUTCLIP	4097	3
PASTEBLOCK	8193	3
WHAT I WANT
Name	ID	group id	repeat	count
CHPROP	8193	3	2
CHPROP	8193	3	2
TEXTEDIT	8193	3	2
TEXTEDIT	8193	3	2
CHPROP	8193	3	9
CHPROP	8193	3	9
CHPROP	8193	3	9
CHPROP	8193	3	9
CHPROP	8193	3	9
CHPROP	8193	3	9
CHPROP	8193	3	9
CHPROP	8193	3	9
CHPROP	8193	3	9
MATCHPROP	8193	3	2
MATCHPROP	8193	3	2

[jimbarbour]

@OLUGBOJI ,

This is reasonably straightforward if one uses BY processing with the NOTSORTED option.  See code, below, and, below the code, the results.

Regards,

Jim

DATA	Have;
	INFILE	DATALINES	DSD	DLM='09'X	MISSOVER;
	INPUT	Name	$
			ID	
			group_id
			;

DATALINES;
QSAVE	4097	3	 	 
OPEN	4097	3	 	 
OPEN	4097	3	 	 
QSAVE	4097	3	 	 
OPEN	4097	3	 	 
PASTECLIP	8193	3	 	 
MTEDIT	4097	3	 	 
GRIP_STRETCH	8193	3	 	 
CHPROP	8193	3	 	 
CHPROP	8193	3	 	 
MATCHPROP	8193	3	 	 
COPY	8193	3	 	 
TEXTEDIT	8193	3	 	 
TEXTEDIT	8193	3	 	 
MOVE	8193	3	 	 
GRIP_STRETCH	8193	3	 	 
MOVE	8193	3	 	 
ZOOM	8193	3	 	 
QSAVE	4097	3	 	 
OPEN	4097	3	 	 
PASTECLIP	8193	3	 	 
CHPROP	8193	3	 	 
CHPROP	8193	3	 	 
CHPROP	8193	3	 	 
CHPROP	8193	3	 	 
CHPROP	8193	3	 	 
CHPROP	8193	3	 	 
CHPROP	8193	3	 	 
CHPROP	8193	3	 	 
CHPROP	8193	3	 	 
MATCHPROP	8193	3	 	 
MATCHPROP	8193	3	 	 
CUTCLIP	4097	3	 	 
PASTEBLOCK	8193	3	 	 
CUTCLIP	4097	3	 	 
PASTEBLOCK	8193	3		
;
RUN;

DATA	Want;
	SET	Have;
	BY	Name	NOTSORTED;

	Repeat	+	1;

	IF	LAST.Name	THEN
		DO;
			OUTPUT;
			Repeat	=	0;
			DELETE;
		END;
	ELSE
		DO;
			DELETE;
		END;
RUN;

[mkeintz]

---

@OLUGBOJI wrote:

Please see below for values of what I have vs what I want. I want to get repeated consecutive names for certain id's and their count and the cummulative count. Proccsql and sql if possible. Thanks

The moment you want to look for consecutive values in a data set, you should not be considering PROC SQL.  It's simply not meant to care about record order.  Yes, you can torture proc sql to do so, but it's just not worth it.  So @jimbarbour 's suggestion of the BY ... NOTSORTED; statement is the way to go.  It is exactly the right technique to detect and process repeated values.

I would suggest a simpler data step, however:

data want;
  set have ;
  by name notsorted;
  repeat+1;
  if first.name then repeat=1;
  if last.name;
run;