topic Re: Get consecutive repeating values from a table in SAS Enterprise Guide

Get consecutive repeating values from a table

OLUGBOJI — Mon, 19 Jul 2021 15:29:05 GMT

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

Re: Get consecutive repeating values from a table

jimbarbour — Fri, 23 Jul 2021 18:01:54 GMT

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

Re: Get consecutive repeating values from a table

mkeintz — Sat, 24 Jul 2021 03:25:02 GMT

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