DATA Step, Macro, Functions and more

Count Distinct Names for Each Quarter

Reply
Regular Contributor
Posts: 151

Count Distinct Names for Each Quarter

I know I've done this before but can't find the code. I have a table listing case managers and I want to create a variable CM_Cnt that I can use to count the number of distinct managers each quarter. I think the code looked something like that below but it ain't working.

 

Proc sql;

create table MEPRS2 as

select *, (1/count(distinct  Case_Manager)) as CM_Cnt

from MEPRS

group by Year, Qtr, Case_Manager

;

quit;

 

Any help greatly appreciated!

Super User
Posts: 6,903

Re: Count Distinct Names for Each Quarter

This ought to be closer:

 

Proc sql;

create table MEPRS2 as

select count(distinct  Case_Manager) as CM_Cnt

from MEPRS

group by Year, Qtr

;

quit;

Regular Contributor
Posts: 151

Re: Count Distinct Names for Each Quarter

Posted in reply to Astounding

Thanks Astounding! I'll give that a try in the morning.

Respected Advisor
Posts: 4,779

Re: Count Distinct Names for Each Quarter

@BTAinVA

You might also want to add Year and Qtr to the select clause.

Proc sql;
  create table MEPRS2 as
  select Year, Qtr,count(distinct  Case_Manager) as CM_Cnt
  from MEPRS
  group by Year, Qtr
  ;
quit;
Regular Contributor
Posts: 151

Re: Count Distinct Names for Each Quarter

Patrick,

 

Thanks for the reply. That's not quite what I want. Let's say there is a case manager AdamsB who shows up 4 times in Qtr 1 2018 then I want CM_Cnt to be 0.25 for each of those four records so that it sums to 1 when I roll it up to get a count of unique case managers.

Regular Contributor
Posts: 151

Re: Count Distinct Names for Each Quarter

Now that I talked myself thru it I came up with the answer:

 

Proc sql;

create table MEPRS2 as

select *, 1/count(Case_Manager) as CM_Cnt

from MEPRS

group by Year, Qtr, Case_Manager

 

;

quit;

 

Respected Advisor
Posts: 4,779

Re: Count Distinct Names for Each Quarter


@BTAinVA wrote:

Patrick,

 

Thanks for the reply. That's not quite what I want. Let's say there is a case manager AdamsB who shows up 4 times in Qtr 1 2018 then I want CM_Cnt to be 0.25 for each of those four records so that it sums to 1 when I roll it up to get a count of unique case managers.


That's what the DISTINCT in the count already does for you.

data MEPRS;
  year=2017;
  do Qtr=1,2;
    Case_Manager='A';
    output;output;output;
  end;
  stop;
run;

Proc sql;
  create table MEPRS2 as
  select Year, Qtr,count(distinct  Case_Manager) as CM_Cnt
  from MEPRS
  group by Year, Qtr
  ;
quit;

Capture.JPG

Ask a Question
Discussion stats
  • 6 replies
  • 137 views
  • 2 likes
  • 3 in conversation