topic Re: Help with full outer join in SAS Programming

Help with full outer join

hhchenfx — Tue, 22 Sep 2020 20:38:36 GMT

Hi Everyone,

I am not sure why my full outer join below keep records appear in both table instead of eliminate them.

Can you please help?

Thanks,

Harry

data Cost_data;
input Product $ cost supplier $;
datalines;
Book 70 ABC
Pencil 3 XYZ
Laptop 1000 AAA
;run;

data Sale_price;
input PRD $ Price Market $ ;
datalines;
Book 1200 MD21201
Pencil 4 VA23344
Phone 1000 MD21102
;
run;

*Put all in 1 code;
proc sql;
create table new
as select * from Cost_data full outer join Sale_price
on Cost_data.product=Sale_price.PRD
;quit;

Re: Help with full outer join

CurtisMackWSIPP — Tue, 22 Sep 2020 20:48:20 GMT

If you don't want the matches do this.

proc sql;
create table new
as select * from Cost_data full outer join Sale_price
on Cost_data.product=Sale_price.PRD
where Cost_data.product ne Sale_price.PRD
;quit;

Re: Help with full outer join

Kurt_Bremser — Tue, 22 Sep 2020 20:50:29 GMT

That's what a full (outer) join does. It keeps all observations, including those where no match is found. If you don't want that, use a left, right or inner join.