Date: Sun, 10 Nov 2002 21:59:22 -0500
Reply-To: Sigurd Hermansen <hermans1@WESTAT.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Sigurd Hermansen <hermans1@WESTAT.COM>
Subject: Re: SAS var lists
At risk or taking the edge off my fabled contentiousness, I'd like to
second the motion. SI has implemented a much richer set of functions,
informats, and formats in SAS SQL than found in other flavors of SQL, why
not introduce some of other conveniences of data step SAS. Other SQL
dialects have added similar dialect-specific functions.
Sig
On Sun, 10 Nov 2002 14:23:37 -0600, Puddin' Man <pudding_man@LYCOS.COM>
wrote:
>I'd _love_ to see *something* like:
>
> array xx(*) {_numeric_/reg-exp/};
>
>as well as similar support for SELECT, but I fear I (and
>many others) may be claimed by the worm or the flame afore
>anysuch should materialize in this world.
>
>These are interesting ideas. A "syntax wrapper" could be a
>considerable improvement.
>
>Yes, it should be as general (and as simple) as possible.
>
>I had this simplistic, simplistic notion as regards SELECT
>and SAS var lists ...
>
>SAS SQL supports the ANSI SQL functions (SUM, AVG, COALESCE,
>etc).
>
>SAS SQL also supports almost all the SAS-specific functions
>not defined in ANSI SQL (TRANWRD, INDEX, etc etc).
>
>SI can add new functions (there's a mess of 'em in V9)
>without violating ANSI SQL standards so long as new function
>names don't coincide with ANSI function names ...
>
>I wonder if SI couldn't add a pseudo-function which conforms
>to the function syntax and merely allows interpretation as a
>SAS rather than an ANSI-SQL expression? Suppose such a
>pseudo-function were named (ahem!) SAS ...
>
>SELECT V1-V4 would interpret as V1 minus V4.
>SELECT SAS(V1-V4) would be interpreted as SELECT V1, V2, V3, V4.
>
>SELECT A: would draw a syntax error.
>SELECT SAS(A:) would select all columns with names beginning
> with an A .
>
>Etc, etc.
>
>And if that were practical (and SI decided to implement),
>perhaps also a pseudo-function named (ahem!) ANSI for non-
>SQL code which interpreted per ANSI standards.
>
> PUT ANSI(v1-v2);
>
>would mean subtract V2 from V1 and output the result.
>
>But they would probably have to enhance PUT to support
>function use for this. Lord only knows if they would have to
>"Move The Earth And The Stars" to achieve such a thang. <g>
>
>Just a flight of fancy on my part. No doubt there are
>reasons why this "wouldn't work". It's rarely as simple
>as one would like it to be ...
>
> Prosit,
> Puddin'
>
>******************************************************
>*** Puddin' Man *** Pudding_Man@lycos.com ********
>******************************************************;
>
>"Blues starts to rolling ...
> stops at my front do'.
> I'm gonna change my way of living ...
> won't have to worry no mo'."
> - from "Blues Before Sunrise", Leroy Carr, maybe 1930
>
>On Fri, 8 Nov 2002 16:55:31
> Howard_Schreier wrote:
>>I think you are right about the need for some syntax extension, but
perhaps
>>it should be more general.
>>
>>Think about a SAS enhancement which is sort of the mirror image of the
>>proposed enhancement to SQL SELECT. Would it not be convenient to be able
to
>>code an expression in a PUT statement, to be evaluated on the fly with the
>>result placed in the output buffer? For example if V1=4.5 and V2=2, then
>>
>> put v1-v2;
>>
>>would write the value 2.5 to the log (or wherever).
>>
>>The problem is context. In a PUT statement, this hyphenated construct is
>>interpreted as a variable list rather than as an expression, so the result
>>would be the two values (4.5,2) written out. There are other ambiguities,
>>such as the slash, which could represent either the division operator or
the
>>line-advance pointer control.
>>
>>What if there were some syntax wrapper which would tell SAS to reverse the
>>default interpretation? Just to illustrate, suppose pointy brackets had
this
>>role. Then
>>
>> put v1-v2;
>>
>>would have the existing meaning while
>>
>> put <v1-v2>;
>>
>>would mean subtract V@ from V1 and output the result.
>>
>>In a context, such as SELECT, where SAS now expects expressions, there
could
>>be a wrapper to reverse that as well. Suppose square brackets were so
>>designated. Then
>>
>> select [v1-v3] ...
>>
>>would be equivalent to
>>
>> select v1, v2, v3 ...
>>
>>The keyword "OF" already has this reversal role for the arguments of the
>>statistical summary functions.
>>
>>On Fri, 8 Nov 2002 15:25:14 GMT, Richard A. DeVenezia
>><radevenz@IX.NETCOM.COM> wrote:
>>
>>>"Puddin' Man" <pudding_man@lycos.com> wrote in message
>>>news:MFOFECMDBKEJHBAA@mailcity.com...
>>>> Lest there be further confusion re my little
>>>> suggestion ...
>>>>
>>>> Reference the V8 Onlinedoc ... "SAS Variable Lists".
>>>>
>>>> The following illustrates the 4 types of generally
>>>> supported var lists:
>>>>
>>>> 759 data xx;
>>>> 760 array x(*) x1 x2 yy x4 x5 x6 (1 2 3 4 5 6);
>>>> 761 put (x:) (=); *** name prefix list ***;
>>>> 762 put (_numeric_) (=); *** special SAS name list***;
>>>> 763 put (x2--x5) (=); *** name range list ***;
>>>> 764 put (x2-x5) (=); *** numbered range list ***;
>>>> 765 run;
>>>>
>>>snip
>>>>
>>>> It had occurred to me that none of these were supported
>>>> in SELECT. Seemed somehow un-SAS-like.
>>>>
>>>> With some prodding from Ian, I realized that "numbered range
>>>> lists" can be inconsistent with ANSI SQL ("slaps his po' self
>>>> on the cheek!" <g>), which may dictate that the hyphen be
>>>> interpreted as a difference operator.
>>>>
>>>snip
>>>
>>>What would be nice is syntactic tokens that are non-ambiguous or conflict
>>>with regard to SQL conventions (and planned conventions) and thus could
be
>>>safe SAS specific extensions to SQL.
>>>
>>>A good candidate is :.
>>>: is typically not part of a variable name nor part of standard SQL
select
>>>statement.
>>>: could be in a variable name that is indicated as a name constant
>>>
>>>options validvarname = any;
>>>data funny;
>>> 'Ha:ha'N = 123;
>>>run;
>>>proc sql;
>>> create table funny2 as
>>> select 'Ha:ha'N from funny;
>>>quit;
>>>
>>>Anyway, consider
>>>select X: from someData
>>>This SAS SQL extension could select all variables starting with X. The
>>>same as well defined behaviour in Data step and procs.
>>>
>>>However, a much more interesting consideration could be
>>>select {table.|alias.}/reg-exp/
>>>Any columns whose names meet the regular expression would be in the
>>>resultant select.
>>>
>>>or possibly
>>>select :{table.|alias.}/reg-exp/
>>>(Reserving / leading expression) for an other future possibility where
>>>regular expressions operate rowwise on the incoming data.
>>>
>>>Send your suggestions to suggest@sas.com
>>>
>>>--
>>>Richard A. DeVenezia
>>>http://www.devenezia.com/downloads/sas/macros
>>
>
>
>__________________________________________________________
>Outgrown your current e-mail service? Get 25MB Storage, POP3 Access,
>Advanced Spam protection with LYCOS MAIL PLUS.
>http://login.mail.lycos.com/brandPage.shtml?pageId=plus&ref=lmtplus
| 
