Here's one way, but the results don't match.
The TEMP data set is very close but you have a person 1123 who has been listed twice which you don't want for ID 1122. What's the logic between those two differing criteria? I'm guessing you likely have a different field you need to include in this logic as well.
This code handles them the same.
data WORK.docs_have(label='Surgery Data');
input rcpt_id:32. service_date:date9. physician_id:32.;
ID = _n_;
format service_date date9.;
datalines;
1356 22NOV2008 3354
1123 13OCT2010 3354
1267 18JAN2011 3354
1123 15AUG2011 3354
1145 30NOV2009 2234
1323 24JAN2010 2234
1122 29JAN2010 2234
1122 13SEP2010 2234
;;;;
proc sql;
create table temp as
select *,
(select count(distinct t2.rcpt_Id)
from docs_have as t2
where t1.physician_id = t2.physician_id
and t1.ID ne t2.ID and
t2.service_date between t1.service_date - 365 and t1.service_date) as num_procs
from docs_have as t1
order by rcpt_id, ID
;
quit;
data want;
set temp;
by rcpt_id;
if first.rcpt_id;
run;
@yubaraj wrote:
Hi I want to get surgeon volume within previous 365 days of the operation on a person.
My objective is to test the hypothesis that if a surgeon performed greater number of operation in the last 1 year then their operation is more likely to succeed.
All I have is records per each service users. The recipient ID can duplicate because if someone has a operation in first leg then they also can have operation in second leg. I am interested in first operation
data WORK.docs_have(label='Surgery Data');
input rcpt_id:32. service_date:date9. physician_id:32.;
format service_date date9.;
datalines;
1356 22NOV2008 3354
1123 13OCT2010 3354
1267 18JAN2011 3354
1123 15AUG2011 3354
1145 30NOV2009 2234
1323 24JAN2010 2234
1122 29JAN2010 2234
1122 13SEP2010 2234
;;;;
data WORK.docs_want(label='Surgery Data_want');
input rcpt_id:32. service_date:date9. physician_id:32. surg_vol:12;
format service_date date9.;
datalines;
1356 22NOV2008 3354 0
1123 13OCT2010 3354 0
1267 18JAN2011 3354 1
1123 15AUG2011 3354 2
1145 30NOV2009 2234 0
1323 24JAN2010 2234 1
1122 29JAN2010 2234 2
;;;;
The variables are
recipient ID (rcpt_id) service date (service_date) and provider ID (physician_id),
data WORK.docs_want(label='Surgery Data_want');
input rcpt_id:32. service_date:date9. physician_id:32. surg_vol:12;
format service_date date9.;
datalines;
1356 22NOV2008 3354 0
1123 13OCT2010 3354 0
1267 18JAN2011 3354 1
1123 15AUG2011 3354 2
1145 30NOV2009 2234 0
1323 24JAN2010 2234 1
1122 29JAN2010 2234 2
