Date:         Mon, 13 Sep 2004 12:37:26 -0500
Reply-To:     Pudding Man <pudding.man@gmail.com>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Pudding Man <pudding.man@GMAIL.COM>
Subject:      Re: sorted?
Comments: To: Yufeng Liu <charlesyufengliu@yahoo.com>
In-Reply-To:  <200409111835.i8BIZpk3017976@listserv.cc.uga.edu>
Content-Type: text/plain; charset=US-ASCII

Just a little, tiny addendum to the general subject material ...

There are several circumstances under which a SAS dataset
can be sorted without the sort flag being set, i.e. reading
pre-sorted data from an external file or creating sorted
data in a DATA step.

DATA and most PROC steps will allow BY-var processing
without the SAS dataset sort flag being set for such pre-
sorted data. They assume the indicated sort order until they
have evidence to the contrary.

PROC SQL is an exception. It assumes no sort order
unless the sort flag is set:

56   %let obs = 1e5;
57
58   data a b;
59    array x(4)  (4 * 0);
60    do id = 1 to &obs;
61     output a;
62     if uniform(9) > .4 then output b;
63     end;
64    run;

NOTE: The data set WORK.A has 100000 observations and 5 variables.
NOTE: The data set WORK.B has 60204 observations and 5 variables.
NOTE: DATA statement used:
      real time           0.57 seconds
      cpu time            0.18 seconds


65
66   proc sql noprint _method;
67    select b.id
68    from a, b
69    where a.id = b.id
70    ;

NOTE: SQL execution methods chosen are:

      sqxslct
          sqxjm
              sqxsort
                  sqxsrc( WORK.B )
              sqxsort
                  sqxsrc( WORK.A )
70 !    quit;
NOTE: PROCEDURE SQL used:
      real time           0.44 seconds
      cpu time            0.43 seconds

Such unnecessary sorting can easily be avoided by either
setting the sort flag with PROC DATASETS or making use of
the SORTEDBY dataset option:

71
72   proc sql noprint _method;
73    select a.id
74    from a(sortedby = id), b(sortedby = id)
75    where a.id = b.id
76    ;

NOTE: SQL execution methods chosen are:

      sqxslct
          sqxjm
              sqxsrc( WORK.B )
              sqxsrc( WORK.A )
76 !    quit;
NOTE: PROCEDURE SQL used:
      real time           0.03 seconds
      cpu time            0.02 seconds

  Salut,
  Puddin'

******************************************************
*** Puddin' Man      PuddingDotMan at GmailDotCom  ***
******************************************************;

"Refried confusion is making itself clear.
 Wonder which way do I go to get on out of here?"
  - from "Right Place, Wrong Time", Mac Rebennack

On Sat, 11 Sep 2004 14:35:50 -0400, Yufeng Liu
<charlesyufengliu@yahoo.com> wrote:
> Hi,
>
> I am quite new to SAS. How do know if a dataset has been sorted? I don't
> want to use proc print to check manually.
>
> Thanks
>
> Yufeng
>