Count the number of years two person are in relationship

Accepted Solution Solved
Reply
Regular Contributor
Posts: 161
Accepted Solution

Count the number of years two person are in relationship

Dear All:

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

PatientYearDoctor
A1992Doctor_1
A1993Doctor_1
B2005Doctor_3
B2006Doctor_2
B2007Doctor_2
B2008Doctor_2

I hope to get my WANT table like this:

PatientYearDoctorTenure
A1992Doctor_11
A1993Doctor_12
B2005Doctor_31
B2006Doctor_21
B2007Doctor_22
B2008Doctor_23

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;


Accepted Solutions
Solution
‎10-09-2013 08:24 AM
Super Contributor
Posts: 339

Re: Count the number of years two person are in relationship

Posted in reply to caveman529

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;

View solution in original post


All Replies
Frequent Contributor
Frequent Contributor
Posts: 83

Re: Count the number of years two person are in relationship

Posted in reply to caveman529

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

Respected Advisor
Posts: 4,173

Re: Count the number of years two person are in relationship

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;

Respected Advisor
Posts: 4,173

Re: Count the number of years two person are in relationship

Posted in reply to caveman529

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;

Solution
‎10-09-2013 08:24 AM
Super Contributor
Posts: 339

Re: Count the number of years two person are in relationship

Posted in reply to caveman529

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;

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 209 views
  • 6 likes
  • 4 in conversation