BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
sasuser77
Calcite | Level 5

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
1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User
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

9 REPLIES 9
LinusH
Tourmaline | Level 20
SQL: Select count(distinct pres_id)
Data never sleeps
Haikuo
Onyx | Level 15

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
Calcite | Level 5

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
Super User

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

 

And same facility is counted only once? 

sasuser77
Calcite | Level 5

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
Super User

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
Super User
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
Super User
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
Calcite | Level 5

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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