topic Re: How to merge?? in SAS Programming

How to merge??

Kristinos — Thu, 27 Nov 2014 08:31:31 GMT

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!

Re: How to merge??

Patrick — Thu, 27 Nov 2014 08:52:54 GMT

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.

Re: How to merge??

Kristinos — Thu, 27 Nov 2014 09:53:03 GMT

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!

Re: How to merge??

RW9 — Thu, 27 Nov 2014 10:03:38 GMT

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

Re: How to merge??

Kristinos — Thu, 27 Nov 2014 12:34:56 GMT

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

Re: How to merge??

Patrick — Thu, 27 Nov 2014 20:11:58 GMT

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

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.