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!
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;
Thanks Astounding! I'll give that a try in the morning.
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;
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.
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;
@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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.