I have two datasets: 1) lab test results, 2) patient ID information. There is one common identifier variable "X" between them.
The dataset with lab test results has 1 test result per case row, therefore, if a patient has multiple tests, they have multiple rows (with the same variable "X"). In the patient ID file each patient only has one row, and therefore there are no duplicates of variable "X" between rows.
I have tried to merge the lab file with the patient ID file by using "Merge Files > Add Variables". For patients who have multiple lab tests, I want the relevant patient ID information to be merged with ALL rows with their lab results. However, what currently happens is that the patient ID information is only merged with the first row of common variable X, and the rest of the rows remains blank.
How can I get the same variable info merged with multiple rows base on one common value in variable X?
Thank you!
File 1 LAB
Variable X Lab
111 CRP
111 Leukocytes
222 CRP
444 CRP
444 Leukocytes
444 Thrombocytes
File 2 PATIENT INFO
Variable X Date of birth Wardnumber
111 01.01.1960 123
222 02.02.1960 456
444 04.04.1960 789
WANT OUTCOME:
Variable X Lab Date of birth Wardnumber
111 CRP 01.01.1960 123
111 Leukocytes 01.01.1960 123
222 CRP 02.02.1960 456
444 CRP 04.04.1960 789
444 Leukocytes 04.04.1960 789
444 Thrombocytes 04.04.1960 789
------------------------------
S B
------------------------------