topic Re: Count Distinct Names for Each Quarter in SAS Programming

Count Distinct Names for Each Quarter

BTAinRVA — Wed, 11 Apr 2018 21:32:56 GMT

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!

Re: Count Distinct Names for Each Quarter

Astounding — Wed, 11 Apr 2018 22:11:31 GMT

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;

Re: Count Distinct Names for Each Quarter

BTAinRVA — Thu, 12 Apr 2018 02:05:17 GMT

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

Re: Count Distinct Names for Each Quarter

Patrick — Thu, 12 Apr 2018 12:47:55 GMT

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

Re: Count Distinct Names for Each Quarter

BTAinRVA — Thu, 12 Apr 2018 13:24:05 GMT

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.

Re: Count Distinct Names for Each Quarter

BTAinRVA — Thu, 12 Apr 2018 13:28:04 GMT

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;

Re: Count Distinct Names for Each Quarter

Patrick — Thu, 12 Apr 2018 22:55:35 GMT

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