BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
alexgouv
Obsidian | Level 7

Hi, 

I am stuck on a problem where I need to find how many accounts make up the top 25% of sales by region.

 

Right now I have the data in this format:

Region     Account     % of Regional Sales     Running total of % of Regional Sales     Count

New York      "Name"         12.5                            12.5                                                        1

New York      "Name2"       10                                22.5                                                        2

...


There are multiple different regions.

 

What I need to do is figure out which observation pushes the running total of % above that threshold of 25% and then output the count variable for that observation. IE I am trying to find out how many accounts it took to get 25% of the sales for the New York region. Then the same for each subsequent region.

 

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
ErikLund_Jensen
Rhodochrosite | Level 12

Hi @alexgouv 

 

If your dataset is sorted on Region + SalesPct descending, and count is in ascending order within the region, then the following code will do the work;

 

data have;
Region='New York'; Account='Name1'; Salespct=12.5; RunningTotal=12.5; count=1; output;
Region='New York'; Account='Name2'; Salespct=10; RunningTotal=22.5; count=2; output;
Region='New York'; Account='Name3'; Salespct=20; RunningTotal=42.5; count=3; output;
Region='New York'; Account='Name4'; Salespct=17.5; RunningTotal=60; count=4; output;
Region='Omaha'; Account='Name2'; Salespct=7.5; RunningTotal=7.5; count=1; output;
Region='Omaha'; Account='Name2'; Salespct=20.5; RunningTotal=27.5; count=2; output;
Region='Omaha'; Account='Name2'; Salespct=12.5; RunningTotal=40; count=2; output;
run;

data want (drop=Salespct RunningTotal) ; set have; by region;
	if RunningTotal >= 25 and lag(RunningTotal) < 25 then output;
run;

- and next time please supply code to create an input data set. Often the better part of the time used to answer a question is taken up by preparing some data to use for testing.

 

View solution in original post

3 REPLIES 3
ErikLund_Jensen
Rhodochrosite | Level 12

Hi @alexgouv 

 

If your dataset is sorted on Region + SalesPct descending, and count is in ascending order within the region, then the following code will do the work;

 

data have;
Region='New York'; Account='Name1'; Salespct=12.5; RunningTotal=12.5; count=1; output;
Region='New York'; Account='Name2'; Salespct=10; RunningTotal=22.5; count=2; output;
Region='New York'; Account='Name3'; Salespct=20; RunningTotal=42.5; count=3; output;
Region='New York'; Account='Name4'; Salespct=17.5; RunningTotal=60; count=4; output;
Region='Omaha'; Account='Name2'; Salespct=7.5; RunningTotal=7.5; count=1; output;
Region='Omaha'; Account='Name2'; Salespct=20.5; RunningTotal=27.5; count=2; output;
Region='Omaha'; Account='Name2'; Salespct=12.5; RunningTotal=40; count=2; output;
run;

data want (drop=Salespct RunningTotal) ; set have; by region;
	if RunningTotal >= 25 and lag(RunningTotal) < 25 then output;
run;

- and next time please supply code to create an input data set. Often the better part of the time used to answer a question is taken up by preparing some data to use for testing.

 

Reeza
Super User
Provide a full sample data set with expected output, preferable as a data step and someone can help you with the code. Some IF/THEN statements. Include more than one region since you need it by region as well, otherwise the answer won't generalize.
ballardw
Super User

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 1298 views
  • 0 likes
  • 4 in conversation