Hi All, Hope someone can help ... I have the following hypothetical dataset: MainAccount ClientNum AlternativeAccount AA 1 A1 AA 1 A2 AA 1 A3 AA 2 A4 AA 2 A3 BB 1 B1 BB 1 B2 CC 1 You will note that main account AA has two clients linked to it and accounts BB and CC have one client linked to each. Account CC has no alternative accounts In account AA, each of the clients have different alternative accounts, but they share 1 (A3) I want to count how many alternative accounts each client has and how many alternatve account each main account has (without double counting A3 for the first account). The problem is counting only the distinct alternative account numbers when looking at a main account level. I tried using a count(case ...) statement but it will always double count the A3 account and i cannot use a nested statement to say only count it once when is is not distinct ... My ideal final dataset needs to like like follow: (that is: NumAccount_AltAccounts for Account AA must show 4 not 5) MainAccount ClientNum NumClient_AltAccounts NumAccount_AltAccounts AA 1 3 4 AA 2 2 4 BB 1 2 2 CC 1 0 0 Thanks!!
... View more