topic Re: how do I join tables? in SAS Programming

how do I join tables?

elained — Mon, 08 Jul 2024 19:41:45 GMT

I have 2 tables that I need to join.

1. Patients

2. LabResults

I want everyone in the Patients table.

I only want LabResults that match records in the Patients table.

The identifier that both tables contain is PatientID

How do I match all records in the Patients table to those that have the same PatientID in the LabResults table?

Re: how do I join tables?

Tom — Mon, 08 Jul 2024 20:04:03 GMT

This is called a MERGE.

data want;
  merge patients lab_results;
  by patientid;
run;

If you want to exclude lab results that are not actually from patients (how did they get into your database then?) you can use the IN= dataset option to create temporary indicator variables to indicate if the the given dataset is contributing to the current observation.

data want;
  merge patients(in=in1) lab_results;
  by patientid;
  if not in1 then do;
    if first.patientid then put 'ERROR: ' patientid= 'found in LABRESULTS but not in PATIENTS.';
    delete;
  end;
run;

Re: how do I join tables?

elained — Mon, 08 Jul 2024 20:06:12 GMT

Thank you so much! My SAS programming skills are rusty from years of not using SAS. I have to re-learn SAS in a hurry! Really appreciate this.