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

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! Smiley Happy

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

View solution in original post

5 REPLIES 5
Patrick
Opal | Level 21

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
Calcite | Level 5

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Calcite | Level 5

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

Patrick
Opal | Level 21

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.

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!

How to Concatenate Values

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.

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
  • 5 replies
  • 709 views
  • 3 likes
  • 3 in conversation