01-21-2015 10:44 AM
I have a dataset that looks like this
I want to know that the maximum number of sub accounts is 4 i.e. account 101 has 3 subaccounts, account 202 has 2 subaccounts and account 303 has 4 subaccounts.
I don't care how many they individually have - or which account has the most - I just want to know that the maximum number of subaccounts ANY of them have is 4.
Ideally id like this in a PROC SQL format if possible please.
01-21-2015 11:02 AM
That should be
count( distinct SUBACC ) as NUM_RECS
group by ACC
in the subquery, as group by ensures distinct values and it is the number of distinct subaccounts that you want to count.
01-21-2015 11:08 AM
Into :VAL, this is the method that from proc SQL you can create a macro variable from the result of the select. The VAL is the macro variable into which the result is placed, so you can use &VAL. later on to check if it is 4 or not. Just guessed that you wanted a mvar for later as there wasn't much info in the original post.