Assign sequence numbers that reset for each policy

[EinarRoed]

I can use the monotonic() function to assign a sequence number for each row. But I want it to reset at each new POLICY_NO.

How can I accomplish this?

POLICY_NO	WANT
1	1
1	2
1	3
1	4
1	5
1	6
1	7
2	1
2	2
2	3
3	1
4	1
4	2
4	3
4	4
4	5
4	6
5	1
5	2

[RW9 · Accepted solution]

Use datastep, easier:

data want;
  set have;
  by policy_no;
  retain want;
  if first.policy_no then want=1;
  else sum(want,1);
run;

[EinarRoed]

Many thanks!

[data_null__]

@rw9 Does "else sum(want,1);" work in a data step?

@EinarRoed it may also be written.

if first.policy_no then seq = 0;
seq + 1;

[RW9]

Nope, just a typo.  Could do:

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

Thats probably easier.