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?
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;
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).
The table on the left was in a random order
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.