DATA Step, Macro, Functions and more

Unique prescriber count

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 17
Accepted Solution

Unique prescriber count

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_idpres_idfac_idremarks
M001P001F001Add 1
M001P001F002Add 0 P001 is already counted above
M001P002F003Add 1
M001P003F003Add 0 P003 is from same facility of P002
M001P003F004Add 0 P003 is already counted above
M001P003F005Add 0 P003 is already counted above
M001P004F002Add 0 P004 is from same facility  of P002. Final count is 2
M002P002F002 add 1
M002P002F003 add 0
M002P003F002 add 0. final count 1

 

Desired output

Mem_IDUnique prescriber count
M0012
M0021

Accepted Solutions
Solution
‎07-29-2016 09:48 AM
Super User
Posts: 10,023

Re: Unique prescriber count

Posted in reply to sasuser77
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;


View solution in original post


All Replies
Super User
Posts: 5,424

Re: Unique prescriber count

Posted in reply to sasuser77
SQL: Select count(distinct pres_id)
Data never sleeps
Respected Advisor
Posts: 3,156

Re: Unique prescriber count

[ Edited ]
Posted in reply to sasuser77

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. 

Occasional Contributor
Posts: 17

Re: Unique prescriber count

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 

Super User
Posts: 19,772

Re: Unique prescriber count

Posted in reply to sasuser77

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

 

And same facility is counted only once? 

Occasional Contributor
Posts: 17

Re: Unique prescriber count

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.

Super User
Posts: 19,772

Re: Unique prescriber count

Posted in reply to sasuser77

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

Super User
Posts: 10,023

Re: Unique prescriber count

Posted in reply to sasuser77
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;

Solution
‎07-29-2016 09:48 AM
Super User
Posts: 10,023

Re: Unique prescriber count

Posted in reply to sasuser77
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;


Occasional Contributor
Posts: 17

Re: Unique prescriber count

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 432 views
  • 3 likes
  • 5 in conversation