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;
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;
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 |
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;
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.