Inner join on multiple tables

[SASdevAnneMarie]

Hello Experts,

I would like to run the Inner join.

My data is : TEST_1, TEST_2 ........TEST_100

I don't know how to indicate the inner join with : if in=IN1 and in=IN2 and and in=IN3 .....and in=IN100 )

I'm starting my code as :

	data result;
	  merge TEST_1 (in=in1) - TEST_100 (in=in100);
	  by  ID;
	  if in=in1 and in=in100  /* ?*/
    run;

Thank you for your help !

[himofhimself]

data result;
	  merge TEST_1 (in=in1) - TEST_100 (in=in100);
	  by  ID;
	  if in1 and in100; /* ?*/
    run;

Another way using proc sql 

PROC SQL;
Create table result as
Select * from test_1 as in1, test_100 as in100
where in.ID = in100.ID;
Quit;

Both codes will  returns rows common to both tables (data sets). 

[SASdevAnneMarie]

Thank you,
But I need to do the inner join for all my tables: test_1, test_2, test_3,....test_99, test_100.

[himofhimself]

data result;
merge TEST_1 (in=in1) - TEST_100 (in=in100);
by ID;
If sum(of in1-in100) =100;
run;
Hope this works

[SASdevAnneMarie]

Thank you !
It doesn't work, seems that I can't write (in=in1) and (in=in100) after the tables names in condition TEST_1 (in=in1) - TEST_100 (in=in100)

[himself]

Have tried to come up with a macro, kindly give it a try🤔

%macro mergeHelper;
     %local i;
     %do I=1 %to 100;
          Test_&i(in=in&i)
      %end;
%mend mergeHelper;

Data merged;
   Merge
      %mergeHelper;
      If sum(of in1-in100)=100;
Run;

[Kurt_Bremser · Accepted solution]

Since each IN= dataset option needs to be coded explicitly, you will need to use a macro and macro loops to create the repeating code.

[SASdevAnneMarie]

Thank you Kurt !