topic Finding the 5th occurrence of an ID in a column in SAS Programming

Finding the 5th occurrence of an ID in a column

themanwith2thum — Wed, 14 Apr 2021 13:19:39 GMT

Hello I have a data set which has a variable ID with the client ID. I want to identify the fifth occurrence of that ID. For example if my data set looks like this I want to populate a variable called "INDICATOR" which will populate a value of 1 on the fifth occurrence. I know of the function first where I could use first.ID to do this for the first time the ID shows up, but is there a fifth function as well? Thanks in advance!

ID NAME INDICATOR

001 apple

001 apple 1

023 bb

023 bb 1

Re: Finding the 5th occurrence of an ID in a column

Kurt_Bremser — Wed, 14 Apr 2021 13:31:19 GMT

data want;
set have;
by id;
if first.id
then count = 1;
else count + 1;
if count = 5 then indicator = 1;
drop count;
run;

The SUM statement

count + 1;

causes an implicit RETAIN of count.

Re: Finding the 5th occurrence of an ID in a column

PaigeMiller — Wed, 14 Apr 2021 13:32:25 GMT

There is no "fifth" function, but you can easily just count and create the indicator variable

Example:

/* UNTESTED CODE */
data want; 
    set have;
    by id;
    if first.id then count=0;
    count+1;
    if count=5 then flag=1;
    drop count;
run;

Re: Finding the 5th occurrence of an ID in a column

mkeintz — Wed, 14 Apr 2021 13:32:51 GMT

data want;
  set have;
  by id;
  if first.id then counter=1;
  else counter+1;
  if counter=5 then put 'found 5th occurrence at ' _n_=  (_all_) (=);
run;

Re: Finding the 5th occurrence of an ID in a column

ballardw — Wed, 14 Apr 2021 14:20:53 GMT

Question: is it the "fifth" occurrence or the LAST? Your example appears to be asking for a last that just happens to be "fifth".

If not you should build examples that show 1) at least 6 elements and 2) what you expect if there are only fewer than 5 for an id and indicator combination.

Re: Finding the 5th occurrence of an ID in a column

PeterClemmensen — Thu, 15 Apr 2021 19:36:02 GMT

Is the data always sorted or grouped by ID?