Date: Thu, 24 Jun 2004 15:46:07 -0500
Reply-To: "Dunn, Toby" <tdunn@TEA.STATE.TX.US>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: "Dunn, Toby" <tdunn@TEA.STATE.TX.US>
Subject: Re: What the heck is UPPER()?
Content-Type: text/plain; charset="us-ascii"
Me thinks (trying to be like Dorfman he he he) it has to with the where
statement and upper function and how SQL processes the data.
Sig, where are you at when we need a SQL guru?
Toby Dunn
-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of
Pardee, Roy
Sent: Thursday, June 24, 2004 3:45 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Re: What the heck is UPPER()?
Ah, cool--thanks. I don't find that in the v8 docs.
But why would UPPER() work so differently from UPCASE() do you think?
I would think that SI would just alias UPCASE rather than re-implement
(poorly!) a function it's had since probably v1...
-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Don
& Susann
Sent: Thursday, June 24, 2004 1:32 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Re: What the heck is UPPER()?
From the V9 documentation
Don
The SQL Procedure
UPPER function
------------------------------------------------------------------------
--------
Converts the case of a character string to uppercase.
See also: LOWER function
------------------------------------------------------------------------
--------
UPPER (sql-expression)
sql-expression must be a character string and is described in
sql-expression.
------------------------------------------------------------------------
--------
Details
The UPPER function operates on character strings. UPPER converts the
case of its argument to all uppercase.
Pardee, Roy wrote:
> I'd love it if someone can shed light on this.
>
> SAS v8.2, running on Win2K pro on a machine w/512MB of ram.
>
> I've got a 46MB file of drugs & want to search the Generic field for
> particular text strings. Being a sql-head, and forgetting for a
> moment that I'm writing SAS code, I write:
>
> proc sql ;
> create table bubba as
> select ndc
> from MyDrugList as n
> where
> upper(n.Generic) LIKE 'TOREMIFENE%' OR
> upper(n.Generic) LIKE 'FASLODEX%' OR
> upper(n.Generic) LIKE 'TAMOX%' OR
> upper(n.Generic) LIKE 'NOLVADEX%' OR
> upper(n.Generic) LIKE 'RALOX%' OR
> upper(n.Generic) LIKE 'EVISTA%' OR
> upper(n.Generic) LIKE 'FARESTON%' OR
> upper(n.Generic) LIKE 'ARIMIDEX%' OR
> upper(n.Generic) LIKE 'ANASTROZOLE%' OR
> upper(n.Generic) LIKE 'FEMARA%' OR
> upper(n.Generic) LIKE 'LETROZOLE%' OR
> upper(n.Generic) LIKE 'GOSERELIN%' ;
> quit ;
>
> Result: all avaiilable ram on my system is quickly consumed by SAS,
> which thereafter complains:
>
> ERROR: PROC SQL statement aborted due to insufficient memory.
>
> I show the code to my supervisor (a non sql-victim), who asks "What
> the heck is UPPER()? You want UPCASE() I think". And he was
> right--if I change those calls to upcase, it runs lickety-split w/no
> significant memory usage at all.
>
> Here's what's weird--If I leave the calls as upper and just pare down
> the # of drugs I'm looking for, this still sucks memory like crazy,
> but runs successfully and returns the same rows as the upcase()
> version. No errors, no notes about how SAS helpfully figured out that
> I meant to say UPCASE & changed it for me--nothing.
>
> I don't find any reference to UPPER() in the master index of the SAS
> docs.
>
> Trying to use UPPER() in a datastep gets me a "ERROR 68-185: The
> function UPPER is unknown, or cannot be accessed.".
>
> It freaks me out! 8^)
>
> Cheers,
>
> -Roy
>
> Roy Pardee
> Research Analyst/Programmer
> Center For Health Studies (Cancer Research Network)
> Group Health Cooperative
> (206) 287-2078
>
> This message and any attached files might contain confidential
> information protected by federal and state law. The information is
> intended only for the use of the individual(s) or entities originally
> named as addressees. The improper disclosure of such information may
> be subject to civil or criminal penalties. If this message reached you
> in error, please contact the sender and destroy this message.
> Disclosing, copying, forwarding, or distributing the information by
> unauthorized individuals or entities is strictly prohibited by law.
>
>
--
Don Stanley, B.SC, Dip O.R.S, MNZCS
Author:: Beyond the obvious with SAS Screen Control Language.
Author:: Solutions for your GUI Applications Development Using SAS/AF
FRAME Technology http://www.syswaregroup.com ,
http://homepages.rootsweb.com/~ashluke
