Building models with SAS Enterprise Miner, SAS Factory Miner, SAS Visual Data Mining and Machine Learning or just with programming

count rows and restart at 1 based on second column

Reply
New User
Posts: 1

count rows and restart at 1 based on second column

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?

Contributor
Posts: 32

Re: count rows and restart at 1 based on second column

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.
Super User
Super User
Posts: 7,942

Re: count rows and restart at 1 based on second column

[ Edited ]

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;

 

Contributor
Posts: 32

Re: count rows and restart at 1 based on second column

rw9,
I stand corrected, data step is much more "elegant". :$
Ask a Question
Discussion stats
  • 3 replies
  • 258 views
  • 1 like
  • 3 in conversation