topic Re: How to join two columns from one table to a different table based matching criteria in SAS Data Management

How to join two columns from one table to a different table based matching criteria

AshJuri — Tue, 07 Apr 2020 00:15:04 GMT

Hi, I am trying to join the columns "Type2" and "Measurement2" from table "Update" to the table "Have". I want the columns to align where column "Subject1" in table "Have" matches column "Subject2" in table "update", and column "Procedure1" in table "Have" matches column "Procedure2" in table "Update". Thank you in advance.

data Have;
	input Subject1 Type1 :$12. Date1 &:anydtdte. Procedure1 :$12. Measurement1;
	format date yymmdd10.;
	datalines;

500   Initial    15 AUG 2017      Invasive    20 
500   Initial    15 AUG 2017     Surface      35   
428   Initial     3 JUL 2017     Outer        10 
765   Initial     20 JUL 2019     Other       19  
610   Initial     17 Mar 2018     Invasive    17 
;

data Update;
	input Subject2 Type2 :$12. Date2 &:anydtdte. Procedure2 :$12. Measurement2;
	format date yymmdd10.;
	datalines;

500   Followup   15 AUG 2018     Invasive     54 
428   Followup    15 AUG 2018      Outer      29 
765   Seventh     3 AUG 2018      Other       13 
500   Followup    3 JUL 2018      Surface     98 
610   Third       20 AUG 2019     Invasive    66  
;

Re: How to join two columns from one table to a different table based matching criteria

PGStats — Tue, 07 Apr 2020 03:24:30 GMT

With a SQL join:

data Have;
    input Subject1 Type1 :$12. Date1 &:anydtdte. Procedure1 :$12. Measurement1;
    format date1 yymmdd10.;
    datalines;

500   Initial    15 AUG 2017      Invasive    20 
500   Initial    15 AUG 2017     Surface      35   
428   Initial     3 JUL 2017     Outer        10 
765   Initial     20 JUL 2019     Other       19  
610   Initial     17 Mar 2018     Invasive    17 
;

data Updt;
    input Subject2 Type2 :$12. Date2 &:anydtdte. Procedure2 :$12. Measurement2;
    format date2 yymmdd10.;
    datalines;

500   Followup   15 AUG 2018     Invasive     54 
428   Followup    15 AUG 2018      Outer      29 
765   Seventh     3 AUG 2018      Other       13 
500   Followup    3 JUL 2018      Surface     98 
610   Third       20 AUG 2019     Invasive    66  
;

proc sql;
create table want as
select have.*, updt.*
from have, updt
where subject1=subject2 and procedure1=procedure2;
quit;

Re: How to join two columns from one table to a different table based matching criteria

andreas_lds — Tue, 07 Apr 2020 05:17:47 GMT

If you don't insist on having two variable with exactly the same values in all observations, i suggest renaming the variables while using a normal merge:

data want_merge;
	merge have updates(rename=(Subject2=Subject1 Procedure2=Procedure1));
	by Subject1 Procedure1;
run;

Both datasets have to be sorted by Subject and Procedure.