Count the number of years two person are in relationship

[caveman529]

Dear All:

I hope to calculate how long a doctor have been working with a patient.  My data HAVE look something like this:

Patient	Year	Doctor
A	1992	Doctor_1
A	1993	Doctor_1
B	2005	Doctor_3
B	2006	Doctor_2
B	2007	Doctor_2
B	2008	Doctor_2

I hope to get my WANT table like this:

Patient	Year	Doctor	Tenure
A	1992	Doctor_1	1
A	1993	Doctor_1	2
B	2005	Doctor_3	1
B	2006	Doctor_2	1
B	2007	Doctor_2	2
B	2008	Doctor_2	3

Could you please tell me what is the best way for doing so?  Thank you-

data WANT;

  set HAVE;

  format _YEAR yymmddn8.;

  by PATIENT DOCTOR YEAR;

  retain _YAER;

  if first.PATIENT then _YEAR = YAER;

  TENSURE = YEAR - _YEAR;

run;

[Mit]

Please try this piece of code:

data have;
  input patient $ year doctor $;
cards;
A 1992 Doctor_1
A 1993 Doctor_1
B 2005 Doctor_3
B 2006 Doctor_2
B 2007 Doctor_2
B 2008 Doctor_2

;;;;
   run;
proc sort data=have;
     by patient doctor;
run;

data want ;
set have;
     by patient  doctor;
     if first.doctor then tenure=1;
     else tenure+1;
run;

proc sort data=want;
     by patient year doctor;
run;

patient	year	doctor	tenure
A	1992	Doctor_1	1
A	1993	Doctor_1	2
B	2005	Doctor_3	1
B	2006	Doctor_2	1
B	2007	Doctor_2	2
B	2008	Doctor_2	3

[Patrick]

What happens if the data looks as below?

data have;

  input patient $ year doctor $;

  datalines;

B 2006 Doctor_2

B 2007 Doctor_2

B 2010 Doctor_2

;

run;

[Patrick]

data have;
  input patient $ year doctor $;
datalines;
A 1992 Doctor_1
A 1993 Doctor_1
B 2005 Doctor_3
B 2006 Doctor_2
B 2007 Doctor_2
B 2008 Doctor_2
;
run;

proc sql feedback;
  create table want as
  select patient, year, doctor, year+1-min(year) as tenure
  from have
  group by patient, doctor
  order by patient, year, doctor
  ;
quit;

[Vince28_Statcan · Accepted solution]

Similar to those above- using data step rather than SQL but still calculating year difference rather than the simple incrementation of Mit

data have;
  input patient $ year doctor $;
cards;
A 1992 Doctor_1
A 1993 Doctor_1
B 2005 Doctor_3
B 2006 Doctor_2
B 2008 Doctor_2
B 2010 Doctor_2

;;;;
   run;
proc sort data=have;
     by patient doctor year;
run;

data want ;
set have;
     by patient  doctor;
     if first.doctor then tenure=1;

     else tenure=tenure+(year-lastyear);

     lastyear=year;

     retain lastyear tenure;

     drop lastyear;
run;

proc sort data=want;

     by patient year doctor;

run;