CODEWITHSUNDEEP
sas
user14878129
user14878129

Reputation: 9

How to prevent left join from returning multiple rows

While using left join in SAS, the right side table have duplicate IDs with different donations. Therefore, it returns several rows. While i only want one row with the highest donated amount.

The code is as follows:

Create table x
As select T1.*,
T2. Donations

From xxx t1
Left join yy t2 on (t1.id = t2.id);
Quit;

Thanks for any help

Upvotes: 0

Views: 344

Answers (2)

Tremah
Tremah

Reputation: 105

You can either work with a subselect which selects only the highest donation for a given ID or you could do some pre work with SAS (which i prefer):

*Order ascending by ID and DONATIONS;
proc sort data=work.t2;
  by ID DONATIONS;
run;

*only retain the dataset with the highest DONATION per ID;
data work.HIGHEST_DONATIONS;
  set work.t2;
  by ID;

  if last.ID then output;
run;

I don't have SAS available right now but it should work. Don't hesitate asking further questions. :)

Upvotes: 0

nkkumawat
nkkumawat

Reputation: 693

IN SAS follow https://stackoverflow.com/a/61486331/8227346

and in mysql you can use partioning with ROW_NUMBER

CREATE TABLE x As select T1.*, T2.Donations 
From xxx t1 
LEFT JOIN
 
(

  SELECT * FROM 
    (
       SELECT
          *,
          ROW_NUMBER() OVER (PARTITION BY id ORDER BY donated_amount DESC) rank
       FROM
        yy
    )
  
  WHERE
    rank = 1
)

t2
 ON (t1.id = t2.id);

More info can be found https://www.c-sharpcorner.com/blogs/rownumber-function-with-partition-by-clause-in-sql-server1

Upvotes: 1

Related Questions