DATA Step, Macro, Functions and more

Assign sequence numbers that reset for each policy

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 90
Accepted Solution

Assign sequence numbers that reset for each policy

 

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

Accepted Solutions
Solution
‎10-21-2016 07:29 AM
Super User
Super User
Posts: 7,988

Re: Assign sequence numbers that reset for each policy

Posted in reply to EinarRoed

Use datastep, easier:

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

View solution in original post


All Replies
Solution
‎10-21-2016 07:29 AM
Super User
Super User
Posts: 7,988

Re: Assign sequence numbers that reset for each policy

Posted in reply to EinarRoed

Use datastep, easier:

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

Re: Assign sequence numbers that reset for each policy

Many thanks!

Respected Advisor
Posts: 3,799

Re: Assign sequence numbers that reset for each policy

@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;
Super User
Super User
Posts: 7,988

Re: Assign sequence numbers that reset for each policy

Posted in reply to data_null__

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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 293 views
  • 2 likes
  • 3 in conversation