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

[EinarRoed]

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. 🙂

[Kurt_Bremser · Accepted solution]

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.

[RW9]

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;