topic How to split a table into two different tables based on another table in SAS Procedures

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

jerry898969 — Tue, 14 Feb 2012 16:38:53 GMT

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

table2

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

table4

Is this possible with a merge in one data step?

Thank you for the help

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

LinusH — Tue, 14 Feb 2012 16:42:58 GMT

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

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

art297 — Tue, 14 Feb 2012 16:45:09 GMT

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

data table1;

input ID;

cards;

;

data table2;

input ID;

cards;

;

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;

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

Haikuo — Tue, 14 Feb 2012 16:46:46 GMT

Like this:

data table1;

input ID;

cards;

;

data table2;

input ID;

cards;

;

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

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

jerry898969 — Tue, 14 Feb 2012 16:53:41 GMT

Art,

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