data table1;
input value1;
datalines;
1
2
3
5
;
run;
data table2;
input value2;
datalines;
1
2
4
;
run;
/*Scenario:1 Join columns having unique values */
/*Merge with Datastep*/
proc sort data=table1;
by value1;
run;
proc sort data=table2;
by value2;
run;
/* inner join with Datastep */
data innerjoin;
merge table1(in=a) table2(in=b);
by value1 value2;
if a and b;
proc print noobs;
run;
/* inner join with proc sql */
proc sql;
select t1.value1, t2.value2
from table1 t1
inner join
table2 t2
on t1.value1=t2.value2;
quit;
/* left join with Datastep */
data leftjoin;
merge table1(in=a) table2(in=b);
by value1 value2;
if a then output;
proc print noobs;
run;
proc sql;
select t1.value1,t2.value2
from table1 t1
left join
table2 t2
on t1.value1=t2.value2;
quit;
/* right join with Datastep */
data rightjoin;
merge table1(in=a) table2(in=b);
by value1 value2;
if b then output;
proc print noobs;
run;
/* right join proc sql */
proc sql;
select t1.value1 ,t2.value2
from table1 t1
right join
table2 t2
on t1.value1=t2.value2;
quit;
/* full join with Datastep */
data fulljoin;
merge table1(in=a) table2(in=b);
by value1 value2;
proc print noobs;
run;
/* full join proc sql */
proc sql;
select t1.value1,t2.value2
from table1 t1
full join
table2 t2
on t1.value1=t2.value2;
quit;
using data step merge for joins i am getting below error
You need to use the same 'by' variables in your dataset:
/* inner join with Datastep */
data innerjoin;
merge table1(in=a) table2(in=b rename=(value2=value1));
by value1;
if a and b;
run;
- Cheers -
If you use multiple variables in a BY statement, all of them need to be contained in all datasets used in the MERGE.
Catch the best of SAS Innovate 2025 — anytime, anywhere. Stream powerful keynotes, real-world demos, and game-changing insights from the world’s leading data and AI minds.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.