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.
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.
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.