Why the sorting outputs are different if we use Proc SORT and Proc SQL in SAS?

[Justin]

This is a question from a previous student, who can figure it out why the results are different? Try it in SAS.

Take the below data set as an example:
—————————————–
Data AAA;
Input ID $ Name $ Age;
Cards;
P777 G 98
P111 A 25
P235 B 36
P333 C 52
P777 X 19
P329 H 41
P777 Y 68
P893 K 88
P777 Z 70
;
Run;

——————————————

When I use Proc Sort to order it:
proc sort  data=AAA; 
by ID ;
run;

The output is:

Obs ID Name Age

1 P111 A 25
2 P235 B 36
3 P329 H 41
4 P333 C 52

5 P777 G 98
6 P777 X 19
7 P777 Y 68
8 P777 Z 70
9 P893 K 88

However, when I use SQL Order/Group to sort it:

proc sql;
create table ZZZ as 
select *
from AAA
order by ID;
quit;

The ZZZ result is:
Obs ID Name Age

1 P111 A 25
2 P235 B 36
3 P329 H 41
4 P333 C 52

5 P777 Y 68
6 P777 X 19
7 P777 G 98
8 P777 Z 70
9 P893 K 88

Please look at the duplicates of ID=P777, the SQL sorting result is different from that sorted by Proc Sort. The duplicates seem to appear in random. Why???

[Patrick]

It looks like that “proc sort” is reading data based on the default order for unsorted records while sql sort load data into database and do a sorting based on most likely “quick sort algorithm” which produces different result than proc sort.

[Justin]

Yes, you are correct. Proc Sort includes one more step in sorting data, its output is also based on the original sequence of the input data.

• This reply was modified 3 years, 5 months ago by  Justin.