BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
jerry898969
Pyrite | Level 9

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

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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;

View solution in original post

4 REPLIES 4
LinusH
Tourmaline | Level 20

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

Data never sleeps
art297
Opal | Level 21

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;

jerry898969
Pyrite | Level 9

Art,

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

Haikuo
Onyx | Level 15

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 2981 views
  • 0 likes
  • 4 in conversation