Unique prescriber count

[sasuser77]

Hi,

i have following data, where i need to count unique prescribers for each member. A prescriber could have been associated with multiple facilites. If a prescriber counted already for a member and there exists a different prescriber for the member from same facility it should still be counted as 1.

Mem_id	pres_id	fac_id	remarks
M001	P001	F001	Add 1
M001	P001	F002	Add 0 P001 is already counted above
M001	P002	F003	Add 1
M001	P003	F003	Add 0 P003 is from same facility of P002
M001	P003	F004	Add 0 P003 is already counted above
M001	P003	F005	Add 0 P003 is already counted above
M001	P004	F002	Add 0 P004 is from same facility  of P002. Final count is 2
M002	P002	F002	add 1
M002	P002	F003	add 0
M002	P003	F002	add 0. final count 1

Desired output

Mem_ID	Unique prescriber count
M001	2
M002	1

[LinusH]

SQL: Select count(distinct pres_id)

[Haikuo]

The simplest way I can think of is the Hash, dumping everything into Hash object, then check and count:

data have;
	input 
		Mem_id$ pres_id$ fac_id$;
	cards;
M001 P001 F001 
M001 P001 F002 
M001 P002 F003 
M001 P003 F003 
M001 P003 F004 
M001 P003 F005 
M001 P004 F002 
M002 P002 F002 
M002 P002 F003 
M002 P003 F002 
;

data want;

	if _n_=1 then
		do;
			dcl hash h();
			h.definekey('pool');

			h.definedone();
		end;
	length pool $ 10;

	set have;
	by mem_id notsorted;

	if first.mem_id then
		do;
			call missing (count);
			rc=h.clear();
		end;

	if h.check(key:pres_id) ne 0 and h.check(key:fac_id) ne 0 then
		count+1;
	pool=pres_id;
	rc=h.add();
	pool=fac_id;
	rc=h.add();

	if last.mem_id then
		output;
	keep mem_id count;
run;

And please be aware: the entrance order of the data MATTERS. So this is why SQL will not work for you in this case. 

[sasuser77]

Thank you Haikuo.  It appears to be working for the above example. I should have been more clear with my requirement.

I am actually looking different count for below example

M003 P116 F116   + 1
M003 P448 F081  + 1
M003 P448 F123  - pres already counted
M003 P031 F387 
M003 P031 F081  - pres is part of F081 facitlity which is already counted 
M003 P031 F123
M003 P031 F133
M003 P165 F081  
M003 P165 F123 - pres is part of F123/F081 , already counted 
M003 P662 F080
M003 P662 F081 - pres is part of F081 facitlity which is already counted 

the code shows count as 4 where as i am expecting only 2.

only first 2 obs are unique, others either prescriber is repeating or one of the facility of new prescriber is already part of counted prescriber 

[Reeza]

What is counted when a prescriber is in multiple facilities, is that 2 or 1? 

And same facility is counted only once? 

[sasuser77]

when prescriber in muti facilitis , is counted as 1.

and the facility which is already belong to a counted prescriber facility is not counted again.

[Reeza]

That's not an easy problem, at least in any solution I've seen.

Here's the pretty much identical problem that was posed last week on SO

http://stackoverflow.com/questions/38494568/data-step-manipulation-based-on-two-fields-conditioning

[Ksharp]

Yes. It is really not easy question. I love this question.
and Hash Table come to rescue .

data have;
	input 
		Mem_id$ pres_id$ fac_id$;
	cards;
M001 P001 F001 
M001 P001 F002 
M001 P002 F003 
M001 P003 F003 
M001 P003 F004 
M001 P003 F005 
M001 P004 F002 
M002 P002 F002 
M002 P002 F003 
M002 P003 F002 
M003 P116 F116  
M003 P448 F081 
M003 P448 F123
M003 P031 F387 
M003 P031 F081
M003 P031 F123
M003 P031 F133
M003 P165 F081  
M003 P165 F123
M003 P662 F080
M003 P662 F081
;
run;

data want;
 if _n_=1 then do;
  if 0 then set have;
  declare hash h(dataset:'have');
  h.definekey('Mem_id','pres_id','fac_id');
  h.definedone();
  
  declare hash h1(dataset:'have',multidata:'y');
  h1.definekey('Mem_id','fac_id');
  h1.definedata('pres_id');
  h1.definedone();
  
  declare hash h2(dataset:'have',multidata:'y');
  h2.definekey('Mem_id','pres_id');
  h2.definedata('fac_id');
  h2.definedone();
 end;
set have;
by Mem_id;
if first.Mem_id then count=0;
if h.check()=0 then count+1;

rc=h1.find();
do while(rc=0);
 rr=h.remove();
 
 rx=h2.find();
 do while(rx=0);
  rr=h.remove();
  rx=h2.find_next();
 end;
 
 rc=h1.find_next();
end;

if last.Mem_id;

keep Mem_id count;
run;

[Ksharp · Accepted solution]

Opps. I found a problem in my last code(I don't know why I can't delete last post ). 
Try this one :




data have;
	input 
		Mem_id$ pres_id$ fac_id$;
	cards;
M001 P001 F001 
M001 P001 F002 
M001 P002 F003 
M001 P003 F003 
M001 P003 F004 
M001 P003 F005 
M001 P004 F002 
M002 P002 F002 
M002 P002 F003 
M002 P003 F002 
M003 P116 F116  
M003 P448 F081 
M003 P448 F123
M003 P031 F387 
M003 P031 F081
M003 P031 F123
M003 P031 F133
M003 P165 F081  
M003 P165 F123
M003 P662 F080
M003 P662 F081
;
run;

data want;
 if _n_=1 then do;
  if 0 then set have;
  declare hash h(dataset:'have');
  h.definekey('Mem_id','pres_id','fac_id');
  h.definedone();
  
  declare hash h1(dataset:'have',multidata:'y');
  h1.definekey('Mem_id','fac_id');
  h1.definedata('pres_id');
  h1.definedone();
  
  declare hash h2(dataset:'have',multidata:'y');
  h2.definekey('Mem_id','pres_id');
  h2.definedata('fac_id');
  h2.definedone();
 end;
set have;
by Mem_id;
if first.Mem_id then count=0;
if h.check()=0 then count+1;

_fac_id=fac_id;
rc=h1.find();
do while(rc=0);
 rr=h.remove();
 
 rx=h2.find();
 do while(rx=0);
  rr=h.remove();
  rx=h2.find_next();
 end;
 
 fac_id=_fac_id;
 rc=h1.find_next();
end;

if last.Mem_id;

keep Mem_id count;
run;

[sasuser77]

Thank you xia. This is so perfect and you are a great life saver.