# Issue

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

- if there are only 2 accounts in the branch - bring the 2 highest incomes from the first branch and 1 from the second account
- if there are 3 accounts in the branch - bring 1 observation per account, which are the highest ones.
- 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)

## 0 Comments:

## Post a Comment

Note: Only a member of this blog may post a comment.