/*****************************************************************************************************************

SAS file name: sortedby_validated_flag
File location: 
_________________________________________________________________________________________________________________

Purpose: To demonstrate how the SORTEDBY and VALIDATED flags can help SAS imporve performace.
Author: Peter Clemmensen
Creation Date: 18/11/2017

This program supports the example page "The Importance of the SAS SORTED and VALIDATED Flag" on SASnrd.com

*****************************************************************************************************************/

/* Sorted=N, Validated=N */
data NotSortedNotValidated;
   do x=1 to 10e7;
      output;
   end;
run;

proc contents data=NotSortedNotValidated;
run;

proc sql noprint;
   create table test1 as
   select a.*
   from NotSortedNotValidated as a, NotSortedNotValidated as b
   where a.x=b.x;
quit;

/* Sorted=Y, Validated=N */
data SortedNotValidated(Sortedby=x);
   do x=1 to 10e7;
      output;
   end;
run;

proc sql noprint;
   create table test2 as
   select a.*
   from SortedNotValidated as a, SortedNotValidated as b
   where a.x=b.x;
quit;

/* Sorted=Y, Validated=Y */
data SortedNotValidated(Sortedby=x);
   do x=1 to 10e7;
      output;
   end;
run;

/*options sortvalidate; Equivalent to the PRESORTED Option in PROC SORT */
proc sort data=SortedNotValidated presorted out=SortedValidated;
   by x;
run;

proc sql noprint;
   create table test3 as
   select a.*
   from SortedValidated as a, SortedValidated as b
   where a.x=b.x;
quit;