## How to split a table into two different tables based on another table

Solved
Super Contributor
Posts: 405

# How to split a table into two different tables based on another table

I have a table called table1.  I want to subset table2 into two different tables based on if the values in table1 match table2

Example

table1

ID

2

3

5

table2

ID

1

2

3

4

5

6

I want to split table2 into table3 and table4

If table2 has an ID that matches table1 then move that row into table3

If table2 does not match any IDs in table1 put those rows in table4

table3

ID

2

3

5

table4

ID

1

4

6

Is this possible with a merge in one data step?

Thank you for the help

Accepted Solutions
Solution
‎02-14-2012 11:45 AM
PROC Star
Posts: 8,164

## How to split a table into two different tables based on another table

If your tables are already sorted by id you could use something like:

data table1;

input ID;

cards;

2

3

5

8

;

data table2;

input ID;

cards;

1

2

3

4

5

6

;

data table3 table4;

merge table1 (keep=id in=one) table2 (in=two);

by id;

if one and two then output table3;

else if two then output table4;

run;

All Replies
Super User
Posts: 5,882

## How to split a table into two different tables based on another table

This can be accomplished quite simple with a data step MERGE using IN= data set options, combined with conditional OUTPUT.

Data never sleeps
Solution
‎02-14-2012 11:45 AM
PROC Star
Posts: 8,164

## How to split a table into two different tables based on another table

If your tables are already sorted by id you could use something like:

data table1;

input ID;

cards;

2

3

5

8

;

data table2;

input ID;

cards;

1

2

3

4

5

6

;

data table3 table4;

merge table1 (keep=id in=one) table2 (in=two);

by id;

if one and two then output table3;

else if two then output table4;

run;

Super Contributor
Posts: 405

## How to split a table into two different tables based on another table

Art,

thank you for the help that did exactly what I wanted.  Thank you to everyone for your help.

Posts: 3,167

## Re: How to split a table into two different tables based on another table

Like this:

data table1;

input ID;

cards;

2

3

5

;

data table2;

input ID;

cards;

1

2

3

4

5

6

;

data want1 want2;

merge table2 (in=t2) table1(in=t1);

by id;

if t1 and t2 then output want1;

if t2 and not t1 then output want2;

run;

proc print;run;

Please note, you probably want to only keep the variables form table2 by using the data step options: data want1 (keep=) want2 (keep=);

Edit: Art just has offered a great solution.

Regards,

Haikuo

🔒 This topic is solved and locked.