Date: Thu, 24 Jun 2004 15:43:39 -0500
Reply-To: Jack Hamilton <JackHamilton@FIRSTHEALTH.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Jack Hamilton <JackHamilton@FIRSTHEALTH.COM>
Subject: Re: What the heck is UPPER()?
Content-Type: text/plain; charset=us-ascii
UPPER and LOWER are supported in 8.2 and later - see
<http://support.sas.com/techsup/unotes/SN/008/008934.html>.
UPPER and UPCASE should do the same thing. I don't know why they
aren't.
Since all of your comparisons are with truncated substrings, it might
be faster to use the truncated equality operator (not tested):
where
upper(n.Generic) eqt 'TOREMIFENE' OR
upper(n.Generic) eqt 'FASLODEX' OR
upper(n.Generic) eqt 'TAMOX' OR
etc.
There are reports of unexpected behavior when one side of an SQL
truncated equality is empty, so you might have to use (not tested):
where
n.generic ne '' and
(upper(n.Generic) eqt 'TOREMIFENE' OR
upper(n.Generic) eqt 'FASLODEX' OR
upper(n.Generic) eqt 'TAMOX' OR
)
etc.
You could also reduce the number of function calls you make (not
tested):
create table bubba (drop=ugeneric) as
select ndc,
upcase(n.generic) as ugeneric
from MyDrugList as n
where
calculated ugeneric LIKE 'TOREMIFENE%' OR
calculated ugeneric 'FASLODEX%' OR
etc.
Unfortunately, it doesn't seem possible to do a truncated comparison
IN, which would be the simplest option (that does work in the data step
(tested):
data;
set sasuser.iris;
where upcase(species) in: ('V');
run;
--
JackHamilton@FirstHealth.com
Manager, Technical Development
Metrics Department, First Health
West Sacramento, California USA
>>> "Pardee, Roy" <pardee.r@GHC.ORG> 06/24/2004 1:17 PM >>>
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.
