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. 🙂
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.
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.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.