Lag function in SAS

[cindyforest7]

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!

[Haikuo · Accepted solution]

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;