Help using Base SAS procedures

Top 10 Table with counts

Occasional Contributor
Posts: 5

Top 10 Table with counts

[ Edited ]

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!

Super User
Posts: 3,918

Re: Top 10 Table with counts

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

Valued Guide
Posts: 591

Re: Top 10 Table with counts

[ Edited ]

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. 

Occasional Contributor
Posts: 5

Re: Top 10 Table with counts

Posted in reply to SuryaKiran

Yes, this worked. Thank you!

Super User
Posts: 3,918

Re: Top 10 Table with counts

Then please mark post as answered.

Ask a Question
Discussion stats
  • 4 replies
  • 1 like
  • 3 in conversation