topic Re: Top 10 Table with counts in SAS Procedures

Top 10 Table with counts

slobber — Sun, 22 Apr 2018 05:30:33 GMT

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!

Re: Top 10 Table with counts

SASKiwi — Sun, 22 Apr 2018 06:57:53 GMT

Something like this - untested?

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

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



           ,

Re: Top 10 Table with counts

SuryaKiran — Sun, 22 Apr 2018 13:05:53 GMT

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

data have;
infile datalines dlm=" " dsd;
input CompName :$ ClientID ProjectID :$;
datalines;
CompanyABC 123 0022
CompanyABC 123 0023
CompanyABC 235 0042
CompanyABC 234 0042
CompanyDEF 52 0074
CompanyDEF 123 0001
;
run;
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 ;
quit;

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

Re: Top 10 Table with counts

slobber — Sun, 22 Apr 2018 15:36:31 GMT

Yes, this worked. Thank you!

Re: Top 10 Table with counts

SASKiwi — Sun, 22 Apr 2018 21:14:17 GMT

Then please mark post as answered.