DATA Step, Macro, Functions and more

Join two tables

Accepted Solution Solved
Reply
Contributor
Posts: 44
Accepted Solution

Join two tables

Hello Everyone,

 

I have to datasets Dataset A and Dataset B. I want to join age to Table A using variables ID and sales. 

 

   Table A         Table B
IDsales IDsalesage
1car 1car11
1bike  1bike 12
1truck  2car23
2car 2bike 23
3bike  3bike 24
3bike  6bike 32
3truck  4car18
6bike  4truck 19
4car 5bike 20
4truck     
5bike     

 

The output should look like below: 

 

IDsalesage
1car11
1bike 12
1truck .
2car23
3bike 24
3bike 24
3truck .
6bike 32
4car18
4truck 19
5bike 20

 

Thank you!


Accepted Solutions
Solution
‎10-03-2016 12:34 AM
Respected Advisor
Posts: 3,889

Re: Join two tables

Now we're talking! See working code below and compare with what you've posted.

data want;
  merge a(in=ina) b;
  by id sales;

  if ina;
run;

proc sql;
  create table want2 as
    select a.*,b.age 
      from a
        left join b
          on a.id=b.id and a.sales= b.sales;
quit;

View solution in original post


All Replies
Respected Advisor
Posts: 3,889

Re: Join two tables

What's the problem? Where do you get stuck?

 

Have you tried a SAS data step MERGE with by group {ID, Sales} or a SQL left join with table B on the right side?

Contributor
Posts: 44

Re: Join two tables

Hello Patrick,

Thanks for quick reply. I tried using both strategies and nothing is working. I want the output as shown above. The merge statement is giiving values from the table B which I wont need. The left join statement I used is giving me different output I expected. Please correct my code if its wrong.

 

data a;
input ID sales$;
datalines;
1	car
1	bike 
1	truck 
2	car
3	bike 
3	bike 
3	truck 
6	bike 
4	car
4	truck 
5	bike 
;
data b;
input ID sales$ age;
datalines;
1	car	    11
1	bike 	12
2	car	    23
2	bike 	23
3	bike 	24
6	bike 	32
4	car	    18
4	truck 	19
5	bike 	20
;
proc sort data= a;
by id sales;
run;
proc sort data= b;
by id sales;
run;

data want;
merge a b;
by id sales;
run;
proc sql;
create table want2 as
select a.*,b.age from a a
left join b b
on a.sales= b.sales;
quit; 

Thanks!

 

Super User
Posts: 17,819

Re: Join two tables

Your SQL join condition is incorrect. You need an = for every BY variable (ID and Sales) 

 

Then you should get the correct results. 

Solution
‎10-03-2016 12:34 AM
Respected Advisor
Posts: 3,889

Re: Join two tables

Now we're talking! See working code below and compare with what you've posted.

data want;
  merge a(in=ina) b;
  by id sales;

  if ina;
run;

proc sql;
  create table want2 as
    select a.*,b.age 
      from a
        left join b
          on a.id=b.id and a.sales= b.sales;
quit;
Contributor
Posts: 44

Re: Join two tables

Thank you Patrick. This code worked.:-)
☑ This topic is SOLVED.

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

Discussion stats
  • 5 replies
  • 375 views
  • 3 likes
  • 3 in conversation