[FIXED] how to choose total of 3 obs per branch?

Issue

I wish to choose only 3 OBS per branch, where I need to answer these rules:

1. if there are only 2 accounts in the branch - bring the 2 highest incomes from the first branch and 1 from the second account
2. if there are 3 accounts in the branch - bring 1 observation per account, which are the highest ones.
3. if there are 4 or more accounts per branch - bring 1 observation per account, , which are the highest ones - whilst not repeating the same account twice

Solution

Hmmm . . . The row_id column seems to be ordering the incomes within an account. So, you should be able to use proc sql, although it is a little messy:

select t.*
from t join
     (select crm_branch_id, count(distinct account_id) as cnt
      from t
      group by crm_branch_id
     ) b
     on b.crm_branch_id = t.crm_branch_id
where (cnt = 1 and t.row_id <= 3) or
      (cnt = 2 and t.row_id = 1 or
       cnt = 2 and t.row_id = 2 and
       t.income = (select max(t2.income)
                 from t t2
                 where t2.crm_branch_id = t.crm_branch_id and
                       t2.row_id = 2
                )
      ) or
      (cnt = 3 and row_id = 1) or
      (cnt > 3 and row_id = 1 and
       (select count(*)
        from t t2
        where t2.crm_branch_id = t.crm_branch_id and
              t2.row_id = 1 and
              t2.income >= t.income
       ) <= 3
      );

The arcane logic in the where clause is to handle the different numbers of accounts:

• If there is one account, take the top three rows.
• If there are two accounts, take the two rows with row_id = 1 and then the top row where row_id = 2.
• If there are three accounts, take the rows with row_id = 1.
• If there are four or more accounts, only consider rows where row_id = 1. Then take the top three of those based on income.

Answered By - Gordon Linoff

Answer Checked By - Mildred Charles (PHPFixing Admin)