Join two tables ????

Accepted Solution Solved
Reply
Contributor
Posts: 39
Accepted Solution

Join two tables ????

HI all,

I have two tables,

data test;

    infile datalines;

    input obs product $ quantity;

    datalines;

    1   A  2

    2   B  3

    3   C  5

    4   D  6

    ;

run;

data test2;

    infile datalines;

    input obs product $ quantity;

    datalines;

    1   A  5

    2   D  8

    ;

run;

I want to create a datasets as follows:

    1   A  5

    2   B  3

    3   C  5

    4   D  8

A and D gets updated value from test2? Is there way to achieve this using PROC SQL??

Thanks for the help in advance..


Accepted Solutions
Solution
‎07-15-2014 05:01 PM
Super Contributor
Posts: 275

Re: Join two tables ????


proc sql;
  update test as a
    set quantity=(select quantity from test2 b where a.product=b.product) where a.product in (select product from test2);
    quit;

or

data test;
  update test test2;
  by product;
  run;
 

View solution in original post


All Replies
Super User
Posts: 17,796

Re: Join two tables ????

Why SQL? This is easier in a data step.

Trusted Advisor
Posts: 1,204

Re: Join two tables ????

Try this.

proc sql;

select a.obs,a.product,COALESCE(b.quantity,a.quantity)

from test a left join test2 b

on a.product=b.product;

quit;

Solution
‎07-15-2014 05:01 PM
Super Contributor
Posts: 275

Re: Join two tables ????


proc sql;
  update test as a
    set quantity=(select quantity from test2 b where a.product=b.product) where a.product in (select product from test2);
    quit;

or

data test;
  update test test2;
  by product;
  run;
 

Contributor
Posts: 39

Re: Join two tables ????

thank you all for your suggestion

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 216 views
  • 4 likes
  • 4 in conversation