BookmarkSubscribeRSS Feed

 

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

7 REPLIES 7
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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?

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Not tried it, but perhaps:

proc sql;

     select     max(count(SUBACC))

     into         :VAL

     from       HAVE

     group by ACC;

quit;

PGStats
Opal | Level 21

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

What does the

INTO   :VAL mean please?

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 764 views
  • 0 likes
  • 3 in conversation