Dear All,
I'm working on a project where I need to establish inferred referral relationships between PCPs and specialists. The way I defined inferred referrals is that if member A visited a PCP followed by a specialist visit within 30 days then there is an inferred referral relationship between that PCP and speclialist.
So I have a dataset with all historical visit information for a bunch of members.
member_id visit_date provider_id provider_type
a 1/1/2014 A PCP
a 1/26/2014 B Specialist
a 2/30/2014 A PCP
a 3/2/2014 B Specialist
b 5/16/2014 C PCP
b 7/02/2014 D Specialist
b 7/27/2014 C PCP
b 8/15/2014 C PCP
b 8/19/2014 D Specialist
... ... ... ...
And I want to produce a referral table containing all the inferred referral relationships among PCP and specialists for all members
member_id PCP Specialist
a A B
a A B
b C D
b C D
If member a visited specialist B within 30 days after visiting PCP A, then there will be a referral link between A and B.
I've written some codes
Data want;
set have;
prior_provider=lag(provider_id);
prior_date=lag(visit_date);
if prior_provider=provider_id then delete;
if prior_provider=. then delete;
run;
However, the codes won't pair a PCP with a specialist that a member didn't subsequently visited but within 30 days of visiting that PCP. For example, a member visited PCP A and then specialist B within 30 days and then spelialist C also within 30 days, the codes will only establish a link between A and B, but not A and C.
Any help and inputs would be much appreciated!!!!
Thanks a bunch!
LAG() may not be what you need. Try the following code as a starter:
data have;
input member_id $ visit_date :mmddyy10. provider_id:$2. provider_type:$20.;
format visit_date :mmddyy10.;
cards;
a 1/1/2014 A PCP
a 1/26/2014 B Specialist
a 2/28/2014 A PCP
a 3/2/2014 B Specialist
b 5/16/2014 C PCP
b 7/02/2014 D Specialist
b 7/27/2014 C PCP
b 8/15/2014 C PCP
b 8/19/2014 D Specialist
;
proc sql;
create table want as
select distinct a.member_id, a.visit_date, a.provider_id as PCP, b.provider_id as Specialist
from have (where=(UPCASE(provider_type)='PCP')) a
left join have (where=(upcase(provider_type)='SPECIALIST')) b
on a.member_id=b.member_id
and (b.visit_date - a.visit_date) between 0 and 30
;
quit;
LAG() may not be what you need. Try the following code as a starter:
data have;
input member_id $ visit_date :mmddyy10. provider_id:$2. provider_type:$20.;
format visit_date :mmddyy10.;
cards;
a 1/1/2014 A PCP
a 1/26/2014 B Specialist
a 2/28/2014 A PCP
a 3/2/2014 B Specialist
b 5/16/2014 C PCP
b 7/02/2014 D Specialist
b 7/27/2014 C PCP
b 8/15/2014 C PCP
b 8/19/2014 D Specialist
;
proc sql;
create table want as
select distinct a.member_id, a.visit_date, a.provider_id as PCP, b.provider_id as Specialist
from have (where=(UPCASE(provider_type)='PCP')) a
left join have (where=(upcase(provider_type)='SPECIALIST')) b
on a.member_id=b.member_id
and (b.visit_date - a.visit_date) between 0 and 30
;
quit;
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.
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.