topic Re: how to eliminate the cartesian result in joins in SAS Data Management

how to eliminate the cartesian result in joins

venkatnaveen — Sun, 14 Dec 2014 13:24:25 GMT

how to eliminate the cartesian result in joins if two datasets are having more than 1 record in 2 datasets (no chance to differentiate uniquely with the existed variables ).

For ex:

data a;

input pid age sex $;
cards;
1 21 m
1 22 f
1 23 m
2 34 f
2 33 f
;
data b;
input pid age Drug $;
cards;
1 21 Placebo
1 22 aceclo
2 34 Placebo
2 33 Diclofe
2 34 notavailable
;

kindly note that ,in the output Need to report all records without missing from either of the datasets.

Re: how to eliminate the cartesian result in joins

Reeza — Sun, 14 Dec 2014 18:19:34 GMT

What would your expected output be for the above data?

Re: how to eliminate the cartesian result in joins

sas_null_ — Sun, 14 Dec 2014 18:33:38 GMT

A proc sql with joins on id and age should work as long as there are no null values in id and age.

PROC SQL;

CREATE TABLE want AS

SELECT t1.pid,

t1.age,

t1.sex,

t2.Drug

FROM A t1

FULL JOIN B t2 ON (t1.pid = t2.pid) AND (t1.age = t2.age);

QUIT;

Re: how to eliminate the cartesian result in joins

PGStats — Mon, 15 Dec 2014 02:50:51 GMT

To "report all records without missing from either of the datasets", you need a full join and the coalesce function on the joined fields:

data a;

input pid age Sex $;

cards;

1 21 m

1 22 f

1 23 m

2 34 f

2 33 f

;

data b;

length Drug $16;

input pid age Drug $;

cards;

1 21 Placebo

1 22 aceclo

2 34 Placebo

2 33 Diclofe

2 34 notavailable

3 27 newDrug

;

proc sql;

select

coalesce(a.pid, b.pid) as Pid,

coalesce(a.age, b.age) as Age,

a.sex,

b.drug

from

a full join

b on a.pid=b.pid and a.age=b.age;

quit;

In a case like above where the joined fields have the same names in both tables and all non-matching fields have distinct names, you can also use a natural full join and save some typing:

proc sql;

select *

from a natural full join b;

quit;

Note, I added a non-matching record in table b to show the full effect.