count rows and restart at 1 based on second column

[AnneDee]

Hi

I need help with a JMP formula.

I have to up count rows (1 to x) based on a value of a second column, which changes after a while. With each change the up count has to start with 1 again.

Value      Kit ID                 Count

2.3          00047                1
0.9          00047                2
1.8          00047                3
0.3          00264               1
5.7          00264               2
3.2          00053               1
0.1          00053                2

0.9          00053                3

8.3          00053                4 

I got them counted in total by using col number() or counted from 1 to the end by using Sequence(), but i cannot make the formula start couting from 1 again after Kit ID changed. 

Anyone who can help me out?

[ccaulkins9]

AnneDee, Have you tried using proc sql in Base SAS I'm a little rusty but I bet you could find some more info around count aggregations in a SAS Community like Base SAS or ODS-something.

[ccaulkins9]

@AnneDee

[RW9]

Edit:

You could probably shrink to:

data want;
  set have;
  by kitid;
  retain count;
  count=ifn(first.kitid,1,sum(count,1);
run;

Assuming your data is sorted:

data want;
  set have;
  by kitid;
  retain count;
  if first.kitid then count=1;
  else count=count+1;
run;

[ccaulkins9]

rw9,
I stand corrected, data step is much more "elegant". :$

[ccaulkins9]

@RW9