BookmarkSubscribeRSS Feed
slobber
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!

4 REPLIES 4
SASKiwi
PROC Star

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;



           ,
SuryaKiran
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 :$;
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. 

Thanks,
Suryakiran
slobber
Calcite | Level 5

Yes, this worked. Thank you!

SASKiwi
PROC Star

Then please mark post as answered.

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!

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.

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
  • 4 replies
  • 828 views
  • 1 like
  • 3 in conversation