joins in Datastep issue

BrahmanandaRao — Mon, 15 May 2023 06:08:39 GMT

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

Re: joins in Datastep issue

Oligolas — Mon, 15 May 2023 07:41:46 GMT

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;

Re: joins in Datastep issue

Kurt_Bremser — Mon, 15 May 2023 08:41:34 GMT

If you use multiple variables in a BY statement, all of them need to be contained in all datasets used in the MERGE.

topic Re: joins in Datastep issue in SAS Programming

joins in Datastep issue

Re: joins in Datastep issue

Re: joins in Datastep issue