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


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!

1 ACCEPTED SOLUTION

Accepted Solutions
Haikuo
Onyx | Level 15

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;

View solution in original post

1 REPLY 1
Haikuo
Onyx | Level 15

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;

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 1 reply
  • 991 views
  • 0 likes
  • 2 in conversation