DATA Step, Macro, Functions and more

Finding the number of times a policy has been part of the same activity

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 89
Accepted Solution

Finding the number of times a policy has been part of the same activity

[ Edited ]

Hello,

 

I'd appreciate some SAS code help (again).

 

First some basic info:

- A Policy can have several rows (one for each Policy Version).

- A Policy can be associated with 1 or more Cases

- Cases can be associated with the same, or different, Activities

 

What I need is a new column which indicates if it's the 1st, 2nd, 3rd, etc., time the policy becomes part of the same activity. This only happen when the policy has two or more cases within that activity (a policy only 'enters into an activity' by means a a case).

 

Examples:

- Policy 587 and 726 only have one case (i.e. they only 'enter into an activity' once), and therefore has WANT=1.

- Policy 699 has two cases: 748 and 889. Both of these cases are associated with the same activity (123). Case 748 was created first (it has the lowest policy version id), therefore it gets WANT=1. Case 889 was created second, and gets WANT=2 (it's the 2nd case associated with the same policy and activity).

 

 

POLICY_ID POLICY_VERSION_ID ACTIVITY_ID CASE_ID WANT
587 1 123 289 1
587 2 123 289 1
587 3 123 289 1
587 4 123 289 1
699 1 123 748 1
699 2 123 748 1
699 3 123 748 1
699 4 123 889 2
699 5 123 889 2
726 1 877 733 1
726 2 877 733 1

 

Hopefully I explained well enough. Thanks for your time. Smiley Happy


Accepted Solutions
Solution
‎11-09-2016 08:31 AM
Super User
Posts: 6,927

Re: Finding the number of times a policy has been part of the same activity

data want;
set have;
by policy_id activity_id case_id;
retain want;
if first.policy_id then want = 1;
if not first.policy_id and first.case_id then want + 1;
run;

I think we'd need more descriptive test data to see what happens with multiple activities within a policy and so on.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Solution
‎11-09-2016 08:31 AM
Super User
Posts: 6,927

Re: Finding the number of times a policy has been part of the same activity

data want;
set have;
by policy_id activity_id case_id;
retain want;
if first.policy_id then want = 1;
if not first.policy_id and first.case_id then want + 1;
run;

I think we'd need more descriptive test data to see what happens with multiple activities within a policy and so on.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Super User
Posts: 7,392

Re: Finding the number of times a policy has been part of the same activity

Hi, it is just a matter of setting your by line, then increment want by 1 for each new by group - pseudocode as not typing that test data in:

data want;  
  set have;
  retain want;
  by policy_id policy_version_id activity_id case_id;
  if first.policy_Id then want=0;
  if first.case_id then want=sum(want,1);
run;
☑ This topic is SOLVED.

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

Discussion stats
  • 2 replies
  • 158 views
  • 2 likes
  • 3 in conversation