SPSS Statistics

 View Only
Expand all | Collapse all

Merging files with same information merged to multiple rows based on one common identifier variable

  • 1.  Merging files with same information merged to multiple rows based on one common identifier variable

    Posted Wed March 29, 2023 07:06 AM

    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
    ------------------------------


  • 2.  RE: Merging files with same information merged to multiple rows based on one common identifier variable

    Posted Wed March 29, 2023 09:56 AM

    Hi. This is what is called a "table lookup". In the dialog, select one-to-many:

    See here if needed for details on how to perform the match.



    ------------------------------
    Rick Marcantonio
    Quality Assurance
    IBM
    ------------------------------



  • 3.  RE: Merging files with same information merged to multiple rows based on one common identifier variable

    Posted Wed March 29, 2023 03:40 PM

    dataset close all.
    get file='ET/file2.sav'.
    *make sure there are no duplicates.
    sort cases by variablex.
    save outfile='ET/temp.sav'.
    execute.

    dataset close all.
    get file='ET/file1.sav'.
    sort cases by variablex.
    match files file=* /table='ET/temp.sav' /by variablex.



    ------------------------------
    Art Jack
    ------------------------------