topic Re: count rows and restart at 1 based on second column in SAS Data Science

count rows and restart at 1 based on second column

AnneDee — Wed, 21 Dec 2016 14:55:54 GMT

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?

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

ccaulkins9 — Wed, 21 Dec 2016 15:01:10 GMT

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.

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

RW9 — Thu, 22 Dec 2016 08:59:57 GMT

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;

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

ccaulkins9 — Wed, 21 Dec 2016 21:12:19 GMT

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

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

ccaulkins9 — Tue, 10 Apr 2018 13:20:50 GMT

@AnneDee

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

ccaulkins9 — Tue, 10 Apr 2018 13:21:13 GMT

@RW9