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?

     Justin updated 3 months ago 2Members · 3 Posts
  • Patrick

    Member
    November 25, 2020 at 11:45 am

    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

      Administrator
      November 25, 2020 at 7:42 pm

      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 months ago by  Justin.
  • Justin

    Administrator
    October 17, 2020 at 11:07 pm

    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???

Log in to reply.

Original Post
0 of 0 posts June 2018
Now