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
;
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.
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;
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.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.