Date difference across rows (same patient, different variables)

[deengyn]

Hi everyone, 

I'm having a bit of trouble formulating what I want to do; I have some idea such as I will have to incorporate aspects such as "IS NOT NULL" but am unsure what general code format I should look into.

Summary_cod, in this case, represents access to a general appointment.

Pl_code represents the date in which the patient received a plastic surgery regarding the breast area.  

Within the same patient, I'd like to look at the date difference between the date of the most recent general appointment and the first incidence of plastic surgery thereafter.  
i.e. Patient #51, I'd like to find the date difference between OBS 1 and OBS3, whereas Patient #205 I'd like to find the date difference between OBS 5 and OBS 6. 

The original code can be found here: 

*First dataset (identifying general appointments and labelling type of appointment recieved);
proc sql;
CREATE TABLE t1a
		AS 
		SELECT
 		   pid,
 		   cod_act,
		   rec_num,
		   serv_date,
	   FROM tablea
	   WHERE
   			pro_spec = '07'
  		 	AND ( cod_act = '01201'
         		 OR cod_act = '01205')
		ORDER BY
		    pid,
		    serv_date;
		quit;

data t1b;
	set t1a;
	length summary_cod$4; 
	if cod_act = '01205' then summary_cod = 'BR'; 
	if cod_act = '01201' then summary_cod = 'SBR'; 
        run; 

*Second dataset (identifying patients who recieved breast-related plastic surgery); 
proc sql;
CREATE TABLE plastic
		AS 
		SELECT
 		   pid,
 		   cod_act,
		   rec_num,
		   serv_date,
	   FROM tablep
	   WHERE
   			pro_spec = '09'
  		 	AND ( cod_act = '01434'
         		 OR cod_act = '01465'
                         OR cod_act = '01408' 
                         OR cod_act = '01403' )
		ORDER BY
		    pid,
		    serv_date;
		quit;

data pl1;
	set plastic;
	length pl_cod$10; 
	if cod_act = '01434' then pl_cod = 'reduction';
	if cod_act = '01465' then pl_cod = 'mastopexy';
        if cod_act = '01408' then pl_cod = 'removal';
        if cod_act = '01403' then pl_cod = 'nipple';
        run; 

*Stacking the appointment codes with the plastic surgery codes;
		data plbr;
		set t1b pl1;
		run;

*Formatting;
		proc sort data = plbr
		out = plb_sorted;
		by pid serv_date rec_num ; 
		run; 

[Kurt_Bremser]

Please post example data in usable form, in a data step with datalines, and post the code in a window opened with the "little running man" icon; it is very hard to code for pictures.

[deengyn]

Apologies, I've since added my code to describe my data structure.
I will keep this in mind for subsequent posts as well!

[biopharma]

Can really use code for the input dataset. I have created for the first patient. A couple of questions.

1. For DIFF, are both dates included - most recent general visit and first plastic procedure? You will need to add a day to it. 

2. Once a procedure is done, do you care about DIFF value for a later procedure for the same patient?

3. Could there be a general visit between procedures? If the dataset is pre-sorted by PID and DATE then the solution could be simpler.

The above are unknown so the solution is based on some assumptions on my part.

data have ;
   infile datalines missover ;
   length pid 8 serv_date 8 summary_cod $3 pl_cod $12 ;
   format serv_date date9. ;
   input pid serv_date:date9. summary_cod $ pl_cod $ ;
   datalines ;
51 26SEP2017 BR .
51 25SEP2017 SBR .
51 19MAR2018 . removal
51 19MAR2018 . mastopexy
;
run ;

data want ;
   do until (last.pid) ;
      set have ;
      by pid ;
      format recent_date date9. ;
      if not missing(summary_cod) then recent_date = max(serv_date,recent_date) ;
      if not missing(pl_cod) and nmiss(diff,done)=2 then 
         do ;
            diff = serv_date-recent_date ;   
            done = 1 ;
         end ;
      *else diff = . ;
      output ;
   end ;
run ;

[deengyn]

Hi @biopharma ! 

Thank you for such an in-depth analysis of my question. 

As for your question: 

1) If I understand you correctly, no both days are not included. 
Therefore, if the general appointment occurs on 15MAY2020 and the plastic surgery occurs on 16MAY2020, the date difference is 1. Whereas, if both appointment and surgery occur on the same day (15MAY2020), the date difference is 0. 

2) Yes, we would like to look into if the patient is followed up with subsequent plastic surgery.
(i.e. mastopexy is a correction surgery that helps with sagging, date from initial surgery until the mastopexy would be looked at in later analyses. Or, date from general appointment to mastopexy may be important as a measure of quality care)

3) Yes there can be a general appointment in between plastic surgery occurrences. 
(i.e. it may be in the form of a consultation with a medical oncologist should the woman find a tumour/lump after plastic surgery. Although uncommon, a trajectory may include 1) plastic surgery consultation 2) plastic surgery 3) general appointment with family physician 4) after referral consultation with an oncologist 5) surgery to remove tumour 6) plastic surgery to maintain shape of breast)

[biopharma]

Hi @deengyn,

This is helpful but need more answers. As a preliminary step, you can take my program for the first patient and add 1 or 2 more patients with the data that you currently HAVE. Next, let us also know what you would like to see as the end result of that dataset with DIFF generated. Let us keep this to convention and call it the WANT dataset.

From what you have clarified in (2) above, I see that you may need more than one DIFF value. DIFF1 - from General Visit. DIFF2 from Initial Surgery. Finally, from (3) would you be able to add a sample patient to cover that scenario and how it would affect DIFF(s) values. You would also need to generate the WANT dataset with code similar to the one I provided for generating the HAVE dataset above.

One last piece. The dataset (picture) from your first post does NOT represent the code that you provided later. If that is the result of the sort below then for Patient 51, the first SERV_DATE value would be 25SEP2017.

*Formatting;
proc sort data = plbr
out = plb_sorted;
by pid serv_date rec_num ;
run;

[Kurt_Bremser · Accepted solution]

Untested code:

data want;
set have;
by pid;
retain date_1;
if first.pid then date_1 = .;
if summary_cod ne "" then date_1 = serv_date;
if summary_cod = "" and pl_cod ne "" and date_1 ne .
then do;
  diff = serv_date - date_1;
  output;
  date_1 = .;
end;
drop date_1;
run;