How to merge??

[Kristinos]

Hi!

Does anyone know how I can merge DATA1 and DATA2 in the example below?

Data1

ID testday yield

20     1     200

20     2     250    

30     1     190

Data2

ID Age     Weight

20     180     200

20     300     300

30     180     200

Output:

ID testday yield Age Weight

20     1     200     180     200

20     1     200     300     300

20     2     250     180     200

20     2     250     300     300

30     1     190     180     200

Thank you!

[Patrick]

That's definitely a study question so you should may be first give it a try yourself and only ask a question when you get really stuck - also posting the non-working code and explaining all your own thinking you've then already done.

[Kristinos]

I have actually tried both the ways I know of, but haven't succeeded.

Guess I should have included it here, sorry.

I know that when merging I should merge by one or several common variables,

but since data1 and data2 have only ID as common variable the basic code:

Data dataNEW;

merge data1 data2

by ID;

does not work, because the output will look like this:

ID testday yield Age Weight

20     1     200     180     200

20     2     250     300     300

30     1     190     180     200

I have also tried the code with the in-statement:

Data dataNEW;

merge data1 (in=a) data2; by ID ; if a=1;run;

My merge skills kind of stops here...

But maybe one solution could be to make a variable called "Age" in data1, and then run a do-statement to give the Age variable the variables 180 and 300...

Then I could use the "normal" by-statement in the merge procedure !

Hmm... I haven't thought of that solution before... Guess it could be done in other ways as well, but it should work.

Thanks Patrick, It actually helps to find a solution only by writing down what the problem is, so I will remember that the next time I have problems in SAS!

[RW9 · Accepted solution]

Well, in SQL it would look something like:

proc sql;

  create table DATA3 as

  select  COALESCE(A.ID,B.ID) as ID,

          A.TESTDAY,

          A.YIELD,

          B.AGE,

          B.WEIGHT

  from    DATA1 A

  full join DATA2 B

  on      A.ID=B.ID;

quit;

An couple of articles on joining/merging:

http://www2.sas.com/proceedings/sugi30/249-30.pdf

http://www.ats.ucla.edu/stat/sas/library/nesug99/bt150.pdf

http://www.nesug.org/Proceedings/nesug08/ff/ff03.pdf

[Kristinos]

Thank you very much for the suggestion and the articles, it seems to work! 

[Patrick]

You're welcome and I really appreciate how you took my first response to your question. Understanding the problem and formulating a proper question is often half of the solution.

Here a few more links which might be helpful.

SAS(R) 9.4 Language Reference: Concepts, Fourth Edition

SAS(R) 9.4 Language Reference: Concepts, Fourth Edition

http://support.sas.com/resources/papers/proceedings09/036-2009.pdf

If you need to get all combinations of matching keys in 1:many or many:many situations then using a SQL is your method of choice.

A data step Merge behaves differently so this was a great exercise to get people thinking and understand the differences between data step Merge and SQL Join.

One could code to get the desired result with a SAS data step either by using a "DOW loop" http://support.sas.com/resources/papers/proceedings09/038-2009.pdf or by using a hash object (and then loop over the matching keys). But compared to how easy this can be achieve with a SQL such data steps approaches are only of academic value.