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!
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
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.
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!
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
Thank you very much for the suggestion and the articles, it seems to work!
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.