Home › Forums › Main Forums › SAS Forum › Why the sorting outputs are different if we use Proc SORT and Proc SQL in SAS?
-
Why the sorting outputs are different if we use Proc SORT and Proc SQL in SAS?
-
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 525 P777 G 98
6 P777 X 19
7 P777 Y 68
8 P777 Z 70
9 P893 K 88However, 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 Age1 P111 A 25
2 P235 B 36
3 P329 H 41
4 P333 C 525 P777 Y 68
6 P777 X 19
7 P777 G 98
8 P777 Z 70
9 P893 K 88Please 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???
-
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.
Log in to reply.