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;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.