Proc SQL MAX Statement

Reply
Frequent Contributor
Posts: 142

Proc SQL MAX Statement

 

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

Super User
Super User
Posts: 7,682

Re: Proc SQL MAX Statement

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;....

Frequent Contributor
Posts: 142

Re: Proc SQL MAX Statement

Hi RW9,

Can this not be done in one step?

Super User
Super User
Posts: 7,682

Re: Proc SQL MAX Statement

Not tried it, but perhaps:

proc sql;

     select     max(count(SUBACC))

     into         :VAL

     from       HAVE

     group by ACC;

quit;

Respected Advisor
Posts: 4,803

Re: Proc SQL MAX Statement

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

PG
Frequent Contributor
Posts: 142

Re: Proc SQL MAX Statement

What does the

INTO   :VAL mean please?

Super User
Super User
Posts: 7,682

Re: Proc SQL MAX Statement

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.

Frequent Contributor
Posts: 142

Re: Proc SQL MAX Statement

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

Ask a Question
Discussion stats
  • 7 replies
  • 294 views
  • 0 likes
  • 3 in conversation