BookmarkSubscribeRSS Feed
Calcite | Level 5

I'm trying to create a table that calculates the top 10 companies based on the number of unique clients they have. I have a CompanyName variable, a ClientID variable, and a ProjectID variable, so there are, for example, 10 observations for Company ABC  because they have 6 client IDs they are working with for a total of 10 different projects (so some of the clients are repeated but I only want to count each unique client). I am not trying to calculate projects, just the number of unique clients for each company and then sorting by the company with the most clients to create the top 10 list. 


My data set looks something like:

CompName.          ClientID.              ProjectID

CompanyABC.         123                      0022

CompanyABC          123                      0023

CompanyABC           235                     0042

CompanyDEF          152                      0074



Ultimately, I want something along the lines of:


Company     Client Count

CompABC         2

CompDEF          2

CompXYZ          1...


I have tried proc summary, report, print, frequent, and sql...I can't seem to get a table that has more than the first observation (which is not even one of the "top" companies). 


Any help is much appreciated!

Opal | Level 21

Something like this - untested?

proc sort data = have
          out = want1 nodupkey;
  by CompName ClientID;

proc sql;
  create table want2 (obs = 10) as
  select CompName
        ,count(*) as Client_Count
  from want1
  group by CompName
  order by descending Client_Count

Meteorite | Level 14

You can use distinct count and OUTOBS= to limit the output dataset. 


data have;
infile datalines dlm=" " dsd;
input CompName :$ ClientID ProjectID :$;
CompanyABC 123 0022
CompanyABC 123 0023
CompanyABC 235 0042
CompanyABC 234 0042
CompanyDEF 52 0074
CompanyDEF 123 0001
proc sql outobs=1;
Create table want as
select CompName,count(distinct ClientID) as Client_Count
from have
group by CompName
order by Client_Count desc ;

 @SASKiwi I don't this obs= is a valid option in PROC SQL. Correct me if I'm wrong. 

Calcite | Level 5

Yes, this worked. Thank you!

Opal | Level 21

Then please mark post as answered.



Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Get the $99 certification deal.jpg



Back in the Classroom!

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

View all other training opportunities.

Discussion stats
  • 4 replies
  • 1 like
  • 3 in conversation