topic Re: Proc SQL MAX Statement in SAS Programming

Proc SQL MAX Statement

anonymous_user — Wed, 21 Jan 2015 15:44:08 GMT

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

Re: Proc SQL MAX Statement

RW9 — Wed, 21 Jan 2015 15:46:40 GMT

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

Re: Proc SQL MAX Statement

anonymous_user — Wed, 21 Jan 2015 16:00:10 GMT

Hi RW9,

Can this not be done in one step?

Re: Proc SQL MAX Statement

PGStats — Wed, 21 Jan 2015 16:02:18 GMT

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.

Re: Proc SQL MAX Statement

anonymous_user — Wed, 21 Jan 2015 16:05:31 GMT

What does the

INTO :VAL mean please?

Re: Proc SQL MAX Statement

RW9 — Wed, 21 Jan 2015 16:06:42 GMT

Not tried it, but perhaps:

proc sql;

select max(count(SUBACC))

into :VAL

from HAVE

group by ACC;

quit;

Re: Proc SQL MAX Statement

RW9 — Wed, 21 Jan 2015 16:08:07 GMT

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.

Re: Proc SQL MAX Statement

anonymous_user — Wed, 21 Jan 2015 16:12:10 GMT

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