topic Re: Join two tables in SAS Programming

Join two tables

danwarags — Sun, 02 Oct 2016 23:17:44 GMT

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
ID	sales		ID	sales	age
1	car		1	car	11
1	bike		1	bike	12
1	truck		2	car	23
2	car		2	bike	23
3	bike		3	bike	24
3	bike		6	bike	32
3	truck		4	car	18
6	bike		4	truck	19
4	car		5	bike	20
4	truck
5	bike

The output should look like below:

ID	sales	age
1	car	11
1	bike	12
1	truck	.
2	car	23
3	bike	24
3	bike	24
3	truck	.
6	bike	32
4	car	18
4	truck	19
5	bike	20

Thank you!

Re: Join two tables

Patrick — Sun, 02 Oct 2016 23:56:25 GMT

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?

Re: Join two tables

danwarags — Mon, 03 Oct 2016 00:36:35 GMT

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!

Re: Join two tables

Reeza — Mon, 03 Oct 2016 00:45:12 GMT

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

Then you should get the correct results.

Re: Join two tables

Patrick — Mon, 03 Oct 2016 00:49:33 GMT

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;

Re: Join two tables

danwarags — Mon, 03 Oct 2016 04:35:23 GMT

Thank you Patrick. This code worked.:-)