topic Re: Inner join on multiple tables in SAS Programming

Inner join on multiple tables

SASdevAnneMarie — Fri, 02 Jul 2021 07:57:23 GMT

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 !

Re: Inner join on multiple tables

himofhimself — Fri, 02 Jul 2021 08:06:16 GMT

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).

Re: Inner join on multiple tables

SASdevAnneMarie — Fri, 02 Jul 2021 08:11:55 GMT

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

Re: Inner join on multiple tables

himofhimself — Fri, 02 Jul 2021 08:25:14 GMT

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

Re: Inner join on multiple tables

SASdevAnneMarie — Fri, 02 Jul 2021 09:31:41 GMT

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)

Re: Inner join on multiple tables

Kurt_Bremser — Fri, 02 Jul 2021 09:40:14 GMT

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.

Re: Inner join on multiple tables

SASdevAnneMarie — Fri, 02 Jul 2021 09:42:28 GMT

Thank you Kurt !

Re: Inner join on multiple tables

himself — Fri, 02 Jul 2021 10:35:50 GMT

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;