Count Distinct Names for Each Quarter

[BTAinRVA]

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!

[Astounding]

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;

[BTAinRVA]

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

[Patrick]

@BTAinRVA

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;

[BTAinRVA]

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.

[BTAinRVA]

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;

[Patrick]

---

@BTAinRVA 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;