Date: Thu, 24 Jun 2004 13:49:20 -0700
Reply-To: "Choate, Paul@DDS" <pchoate@DDS.CA.GOV>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: "Choate, Paul@DDS" <pchoate@DDS.CA.GOV>
Subject: Re: What the heck is UPPER()?
Hi Roy -
SAS doesn't replace UPPER with UPCASE, it handles it some other way that
interferes with the optimizer, take a look below. You'll need someone else
to explain the tree, or what "***say special 326***" is compared to
upcase(name).
417 proc sql _method _tree feedback stimer ;
NOTE: SQL Statement used:
real time 0.00 seconds
cpu time 0.00 seconds
418 create table jays as
419 select *
420 from sashelp.class as c
421 where
422 upcase(c.name) LIKE 'JA%' OR
423 upcase(c.name) LIKE 'JO%' ;
NOTE: Statement transforms to:
select C.Name, C.Sex, C.Age, C.Height, C.Weight
from SASHELP.CLASS C
where UPCASE(C.Name) like 'JA%' or UPCASE(C.Name) like 'JO%';
NOTE: SQL execution methods chosen are:
sqxcrta
sqxsrc( SASHELP.CLASS(alias = C) )
Tree as planned.
/-SYM-V-(c.Name:1 flag=0001)
/-OBJ----|
| |--SYM-V-(c.Sex:2 flag=0001)
| |--SYM-V-(c.Age:3 flag=0001)
| |--SYM-V-(c.Height:4 flag=0001)
| \-SYM-V-(c.Weight:5 flag=0001)
/-SRC----|
| |--TABL[SASHELP].class opt=''
| | /-FUNC--(UPCASE:0)
| | /-FLST---|
| | | \-NAME--(Name:1)
| | /-LIKE---|
| | | \-LITC('JA%')
| \-LOR----|
| | /-FUNC--(UPCASE:0)
| | /-FLST---|
| | | \-NAME--(Name:1)
| \-LIKE---|
| \-LITC('JO%')
--SSEL---|
NOTE: Table WORK.JAYS created, with 5 rows and 5 columns.
NOTE: SQL Statement used:
real time 0.01 seconds
cpu time 0.00 seconds
424 quit ;
NOTE: PROCEDURE SQL used:
real time 0.00 seconds
cpu time 0.00 seconds
425 proc sql _method _tree feedback stimer ;
NOTE: SQL Statement used:
real time 0.00 seconds
cpu time 0.00 seconds
426 create table jays as
427 select *
428 from sashelp.class as c
429 where
430 upper(c.name) LIKE 'JA%' OR
431 upper(c.name) LIKE 'JO%' ;
NOTE: Statement transforms to:
select C.Name, C.Sex, C.Age, C.Height, C.Weight
from SASHELP.CLASS C
where ***say special 326*** like 'JA%' or ***say special 326***
like 'JO%';
NOTE: SQL execution methods chosen are:
sqxcrta
sqxfil
sqxsrc( SASHELP.CLASS(alias = C) )
Tree as planned.
/-SYM-V-(c.Name:1 flag=0001)
/-OBJ----|
| |--SYM-V-(c.Sex:2 flag=0001)
| |--SYM-V-(c.Age:3 flag=0001)
| |--SYM-V-(c.Height:4 flag=0001)
| \-SYM-V-(c.Weight:5 flag=0001)
/-FIL----|
| | /-SYM-V-(c.Name:1 flag=0001)
| | /-OBJ----|
| | | |--SYM-V-(c.Sex:2 flag=0001)
| | | |--SYM-V-(c.Age:3 flag=0001)
| | | |--SYM-V-(c.Height:4 flag=0001)
| | | \-SYM-V-(c.Weight:5 flag=0001)
| |--SRC----|
| | \-TABL[SASHELP].class opt=''
| | /-SYM-V-(c.Name:1)
| | /-FLUP---|
| | /-LIKE---|
| | | \-LITC('JA%')
| \-LOR----|
| | /-SYM-V-(c.Name:1)
| | /-FLUP---|
| \-LIKE---|
| \-LITC('JO%')
--SSEL---|
NOTE: Table WORK.JAYS created, with 5 rows and 5 columns.
NOTE: SQL Statement used:
real time 0.01 seconds
cpu time 0.00 seconds
432 quit ;
NOTE: PROCEDURE SQL used:
real time 0.00 seconds
cpu time 0.00 seconds
hth
Paul Choate
DDS Data Extraction
(916) 654-2160
-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Pardee,
Roy
Sent: Thursday, June 24, 2004 1:17 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: What the heck is UPPER()?
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.
