If then do loop based on two conditions

[jak1351]

I have a dataset in the form of this:

Patient ID    Contact date     Department_old

1                  march1             Cardiology

1                 march 1             Radiology

2                 jan 1                  Surgery

2                 jan 1                  Endocrinology

3                 feb 1                 internal medicine

etc.

I want to create a new variable called department_new where all the "radiology" and "lab" departments are allocated to the previous defined department like cardiology, surgery etc.

So in short a code like:

if the patient id and contact date is same, then department_new is department_old only if department_old is NOT EQUAL to radiology or lab.

If department_old IS EQUAL to radiology or lab, then department_new = previous/last department_old.

For instance in the above example for patient id 1, on march 1, department_new row 1 should be cardiology but row 2 should not be radiology but one previous which is cardiology.

I tried using this code, but it gives an error, or doesn't produce the output that I want.

data test2 (keep=PatientID ContactDTS Department_old dept_new); set test;
by PatientID ContactDTS;
if First.PatientID then do; dept_new=Department_old ; end;
if First.ContactDTS then do; if Department_old in ('Radiology', 'lab') then dept_new=last.Department_old ;; end;
else dept_new=Department_old ;
run;

Does it make sense?

Thanks in advance.

[DanielLangley]

On mobile at the moment so code is untested.

Data work.want;
Set test;
By patientID;
Retain dept_new;
If first.patientID then dept_new = department_old;
If department_old not in ('Radiology','lab') then dept_new = department_old;
Run;

[Astounding]

I'm not sure you have thought out the problem sufficiently.

Consider the data for the first patient.  Both departments have data for the same date.  But the data might be entered in the opposite order, where "Radiology" comes first, and "Cardiology" comes second.  In that case, shouldn't the Radiology record still treat "Cardiology" as the previously-defined department?  You would need to be able to take some additional step (either looking both forward and backward, or possibly re-sorting the data ... there are several possible choices here) in order to identify the proper department.

[ErikLund_Jensen · Accepted solution]

Hi @jak1351 

I know from experience that the sequence of registrations from different departments doesn't always follow the sequence of patient transfers form department to department, so finding the referring department based on registration order must be taken with some reservation.

I notice that your data example is not real data, and from the name ContactDTS in your code I assume that it might be a timestamp and not a data in your real data. If this is the case then first.ContactDTS is always true, so it doesn't make sense to use it in a condition. If you want the code to work in date groups, it is necessary to recode it to a date or add a date variable to use in the first.-construct.

You cannot refer to last.Department_old, because it is not included in your by-list, and it vould not work the way you expect, because last.something always returns a boolean value 1/0, not the previous value of a variable. You need to keep it in a new retained variable.

I think that you have got the right idea, so the following code has a few modifications to handle the issues mentioned above.

data test1; 
	length PatientID 8 ContactDTS 8 Department_old $20;
	informat ContactDTS datetime.;
	format ContactDTS datetime8.;

	input PatientID ContactDTS Department_old $;
	datalines;
1 01jan2018:09:00:00 Cardiology
1 01jan2018:10:00:00 Radiology
2 01feb2018:09:00:00 Radiology
2 01feb2018:10:00:00 Cardiology
2 01jun2018:08:00:00 Surgery
3 01mar2018:10:00:00 Endocrinology
3 01mar2018:11:00:00 Lab
3 01mar2018:15:00:00 Cardiology
3 01apr2018:14:00:00 Radiology
3 01apr2018:17:00:00 Surgery
;
run;

data tmp; set test1; 
	format ContactDTS ddmmyy10.;
	ContactDTS = datepart(ContactDTS);
run;

data test2 (keep=PatientID ContactDTS Department_old dept_new); set tmp;
	by PatientID ContactDTS;
	length dept_new $20;
	retain dept_new;
    if first.ContactDTS then dept_new = '';
	if Department_old not in ('Radiology', 'Lab') then dept_new = Department_old;
run;

[jak1351]

Thank you so much @ErikLund_Jensen.

I used your solution and got exactly the results that I wanted. Except that where the department was radiology/lab etc. I got the values for the new department to be missing.

So I deleted this line from the code

 if first.ContactDTS then dept_new = '';

sorted my dataset based on patient id, contact date and department old, used your code and got exactly this result.

Where the department is radiology, or lab, in the same patient on the same date, then use the department_new equal to the department_old based on the previous row of same patient and same date.

Thanks so much for the help! I really appreciate it.