Proc SQL MAX Statement

[#1]

I have a dataset that looks like this

acc subacc

101 1011

101 1012

101 1013

202 2021

202 2022

303 3031

303 3032

303 3033

303 3034

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.

Thanks

[RW9]

Hi,

proc sql;

     select     max(NUM_RECS)

     into         :VAL

     from          (

                         select     distinct ACC,

                                        count(SUBACC) as NUM_RECS

                         from       HAVE

                         group by ACC

                         );

quit;

Then use it as:

data xyz;

     if &VAL=4 then do;....

[#3]

Hi RW9,

Can this not be done in one step?

[RW9]

Not tried it, but perhaps:

proc sql;

     select     max(count(SUBACC))

     into         :VAL

     from       HAVE

     group by ACC;

quit;

[PGStats]

That should be

select ACC,

          count( distinct SUBACC ) as NUM_RECS

from HAVE

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.

PG

[#6]

What does the

INTO   :VAL mean please?

[RW9]

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.

[#8]

Thanks for you'r help, I will look to try them out.