## Count the number of years two person are in relationship

Solved
Regular Contributor
Posts: 161

# 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

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;

All Replies
Frequent Contributor
Posts: 83

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

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

Posts: 4,736

## 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;

Posts: 4,736

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

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

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.