Date:         Mon, 24 Nov 2003 10:49:56 -0500
Reply-To:     "Richard A. DeVenezia" <radevenz@IX.NETCOM.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Richard A. DeVenezia" <radevenz@IX.NETCOM.COM>
Subject:      Re: ordering data in a table viewer

"Bryan" <bryan.munday@parexel.com> wrote in message
news:6204037c.0311240707.4bfd6c7d@posting.google.com...
> HI,
>
> I'm unsng SAS/AF v8.2 on VMS.  I am writing an application part of
> whihc will allow users to view datasets that have been created.  I
> have been able to use the COLUMNORDER and WHERE atributes of a data
> set model in order to allow the user to select the columns to view and
> apply any where clauses, however I can see now way to sort the data as
> the user requires.  Is this possible using the data set model
> attributes/methods?.  If not is there a way to get the dataset id for
> the open datset and then use a SORT in order to sort the data?.
>
> TIA
>
> Bryan

Having a dynamic where clause and dynamic sort order is problematic for the
model.
You can use the _sort method to sort the raw data on disk, but that is often
undesirable or disallowed. Additionally, per SAS Data Set Model help:
"When a permanent or temporary WHERE clause is in effect, the _sort method
returns an error. "

So what to do? Use a run-time generated view.

You can have a submit block that creates a view that applies the where and
ordering criteria and then assign that view to the models .table attribute.

presuming whereClause and ordering are either SCL variables or macro
variables whose values replace their tokens in the submit block.  SCL
variables take precedence over macro variables.

submit continue sql;
  create view WORK.CUSTOMVIEW as
  select * from rawdata
  where &whereClause
  order by &ordering
  ;
endsubmit;

myModel.table = ""; * close whatever is showing in the viewer;
myModel.table = "WORK.CUSTOMVIEW";

--
Richard A. DeVenezia
http://www.devenezia.com/downloads/sas/af