BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
caveman529
Calcite | Level 5

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;

1 ACCEPTED SOLUTION

Accepted Solutions
Vince28_Statcan
Quartz | Level 8

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

4 REPLIES 4
Mit
Calcite | Level 5 Mit
Calcite | Level 5

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

Patrick
Opal | Level 21

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;

Patrick
Opal | Level 21

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;

Vince28_Statcan
Quartz | Level 8

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;

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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