topic Re: How can I keep the order of rows the same after a left join? in SAS Programming

How can I keep the order of rows the same after a left join?

aalluru — Mon, 29 Nov 2021 13:55:50 GMT

I'm joining table a with table b using a left join and I want the resultant table to be in the same order that table a was in. However the order seems to be changing to the ascending order of the variable that I used to join on. How can I prevent that from happening?

Re: How can I keep the order of rows the same after a left join?

maguiremq — Mon, 29 Nov 2021 14:16:31 GMT

Do you know what the table on the left was ordered by originally? You can use an `ORDER BY` clause after your join if it's that simple.

You'd probably need to specify the variable that it was sorted on along with its alias (e.g., ORDER BY a.unique_id).

Re: How can I keep the order of rows the same after a left join?

aalluru — Mon, 29 Nov 2021 14:33:43 GMT

The table on the left was in a random order

Re: How can I keep the order of rows the same after a left join?

FreelanceReinh — Mon, 29 Nov 2021 14:39:43 GMT

Hi @aalluru,

In this case create your own (temporary) sort key and use that in the ORDER BY clause.

Example:

/* Create sample data for demonstration */

data have_a;
set sashelp.classfit;
keep name predict;
run;

data have_b;
set sashelp.class;
keep name age;
run;

/* Create view with a sort key (sequential number) */

data _have_a / view=_have_a;
set have_a;
_seqno=_n_;
run;

/* Perform left join maintaining sort order of HAVE_A and delete the view */

proc sql;
create table want(drop=_seqno) as
select a.*, age
from _have_a a left join have_b b
on a.name=b.name
order by _seqno;

drop view _have_a;
quit;

Re: How can I keep the order of rows the same after a left join?

aalluru — Mon, 29 Nov 2021 14:56:48 GMT

Yes! This worked! Thank you!!