How do I maintain the original order of table 'a' in a "left join on" in a SQL Statement? My left join sql statement returns all of the table a values in alphabetic order. I want to maintain the input data order of table 'a'.
proc sql; drop table work.have; quit;
data work.have;
input name $;
cards;
john
jane
jack
jill
jenn
jeff
john
john
jill
jenn
jack
jack
jane
jack
jack
jane
jenn
jenn
;
run;
proc freq data=have order=data noprint;
tables name / missing nocum nopercent out=havecount;
run;
/*** maintains original order ***/
proc sql;
drop table work.originalorder;
CREATE TABLE work.originalorder as
SELECT a.name,
(select distinct count from work.havecount as b
where a.name = b.name) as count
FROM work.have as a
;
quit;
/*** changes the order of the data to alphabetical order ***/
proc sql;
drop table work.alphaorder;
CREATE TABLE work.alphaorder as
SELECT a.name,
b.count
FROM work.have as a left join work.havecount as b
on a.name = b.name
;
quit;
options nocenter;
title " work.originalorder ";
proc print data=work.originalorder (obs=97);
run;
title " ";
title " work.alphaorder ";
proc print data=work.alphaorder (obs=97);
run;
title " ";
work.originalorder
Obs name count
1 john 3
2 jane 3
3 jack 5
4 jill 2
5 jenn 4
6 jeff 1
7 john 3
8 john 3
9 jill 2
10 jenn 4
11 jack 5
12 jack 5
13 jane 3
14 jack 5
15 jack 5
16 jane 3
17 jenn 4
18 jenn 4
work.alphaorder
Obs name COUNT
1 jack 5
2 jack 5
3 jack 5
4 jack 5
5 jack 5
6 jane 3
7 jane 3
8 jane 3
9 jeff 1
10 jenn 4
11 jenn 4
12 jenn 4
13 jenn 4
14 jill 2
15 jill 2
16 john 3
17 john 3
18 john 3
Hi,
SQL is a logical set language, doesn't care about order much. If you want to insist that your order be maintained, suggest you add a rownum variable to work.have, then order by rownum.
HTH,
-Q.
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 the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.