Sorted views (was: Sort order change from Mainframe to Windows)

Howard Schreier's post in the "Sort order change from Mainframe to
Windows" thread contains a very interesting aspect regarding views
and sort:

> Howard Schreier

<some lines snipped ... RB>

> Boston, with an upper case "B", would appear before
> "atlanta" in a simple sort.
>
> So create a view with "hidden" sort keys:
>
> proc sql;
> create view temp_in_report_order as
> select *
> from temp
> order by anydigit(substr(region,1,1) )
> , upcase(region)
> , region;
> quit;
>
> The first ORDER key pushes the digit strings to the
> bottom. The second
> ignores case.
>
> The view looks like this:
>
> region runame
>
> atlanta atlanta
> Boston Boston
> dallas dallas
> newyork newyork
> 100 100
> 500 500
>
> Then code:
>
> proc print data=temp_in_report_order;
> by region notsorted;
> run;
>

Learned something new today (though I could/should have known it).
Quite often I refrained from using views since "you can't create
sorted views" (meaning: "you can't sort views onto themselves" or
even "you can't create a view as output from proc sort"). I was
wrong: while I can't use datastep views for that purpose I can
very well use SQL views for that purpose!

Knowledge beats experiments here (as most everybody who tried to
develop fool-proof code knows, the universe will win this contest,
so even intensive experimenting may miss exactly the one case
where my assumptions go wrong), so I ask this august group: would
you consider the use of sorted SQL views safe for production jobs
("jobs" in the sense of "batch SAS executions") ??

TIA for your thoughts

Robert Bardos
Ansys AG, Zurich, Switzerland

On Apr 1, 8:36 am, bard...@ANSYS.CH (Robert Bardos) wrote:
> Howard Schreier's post in the "Sort order change from Mainframe to
> Windows" thread contains a very interesting aspect regarding views
> and sort:
>
> > Howard Schreier
>
> <some lines snipped ... RB>
>
>
>
>
>
> > Boston, with an upper case "B", would appear before
> > "atlanta" in a simple sort.
>
> > So create a view with "hidden" sort keys:
>
> > proc sql;
> > create view temp_in_report_order as
> > select *
> > from temp
> > order by anydigit(substr(region,1,1) )
> > , upcase(region)
> > , region;
> > quit;
>
> > The first ORDER key pushes the digit strings to the
> > bottom. The second
> > ignores case.
>
> > The view looks like this:
>
> > region runame
>
> > atlanta atlanta
> > Boston Boston
> > dallas dallas
> > newyork newyork
> > 100 100
> > 500 500
>
> > Then code:
>
> > proc print data=temp_in_report_order;
> > by region notsorted;
> > run;
>
> Learned something new today (though I could/should have known it).
> Quite often I refrained from using views since "you can't create
> sorted views" (meaning: "you can't sort views onto themselves" or
> even "you can't create a view as output from proc sort"). I was
> wrong: while I can't use datastep views for that purpose I can
> very well use SQL views for that purpose!
>
> Knowledge beats experiments here (as most everybody who tried to
> develop fool-proof code knows, the universe will win this contest,
> so even intensive experimenting may miss exactly the one case
> where my assumptions go wrong), so I ask this august group: would
> you consider the use of sorted SQL views safe for production jobs
> ("jobs" in the sense of "batch SAS executions") ??
>
> TIA for your thoughts
>
> Robert Bardos
> Ansys AG, Zurich, Switzerland- Hide quoted text -
>
> - Show quoted text -

no problem there at all !
I have a "favourite view"
sasuser.vmacro
It is almost the same as sashelp.vmacro, except that it uses "order
by" to sort the data returned from dictionary.macros .... by
name ... as in the code below
proc sql noprint ;
create view sasuser.vmacro as
select name, value, scope, offset
from DICTIONARY.MACROS
order by scope desc, name asc, offset asc;
quit;