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

Count the number of years two person are in relationship

caveman529 — Wed, 09 Oct 2013 04:08:46 GMT

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;

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

Mit — Wed, 09 Oct 2013 05:22:47 GMT

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

1992

Doctor_1

1993

Doctor_1

2005

Doctor_3

2006

Doctor_2

2007

Doctor_2

2008

Doctor_2

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

Patrick — Wed, 09 Oct 2013 06:33:21 GMT

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;

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

Patrick — Wed, 09 Oct 2013 06:36:33 GMT

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;

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

Vince28_Statcan — Wed, 09 Oct 2013 12:24:45 GMT

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;