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
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;....
Hi RW9,
Can this not be done in one step?
Not tried it, but perhaps:
proc sql;
select max(count(SUBACC))
into :VAL
from HAVE
group by ACC;
quit;
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
What does the
INTO :VAL mean please?
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.
Thanks for you'r help, I will look to try them out.
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.