LISTSERV at the University of Georgia
Menubar Imagemap
Home Browse Manage Request Manuals Register
Previous messageNext messagePrevious in topicNext in topicPrevious by same authorNext by same authorPrevious page (June 2004, week 4)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
Date:         Thu, 24 Jun 2004 16:07:57 -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()?
Comments: To: pardee.r@GHC.ORG
Content-Type: text/plain; charset=us-ascii

There's a little bit of documentation in TS-553, "SQL Joins - The Long and The Short of It", <http://support.sas.com/techsup/technote/ts553.html>.

It's also been discussed at SUGI - use <http://www.lexjansen.com/sugi/> to search for _method. In particular, see Kirk Lafler's paper at <http://www2.sas.com/proceedings/sugi28/019-28.pdf>.

-- JackHamilton@FirstHealth.com Manager, Technical Development Metrics Department, First Health West Sacramento, California USA

>>> "Pardee, Roy" <pardee.r@GHC.ORG> 06/24/2004 1:56 PM >>> Very interesting--many thanks for this.

So how does one learn to interpret these trees, anyhow? I spent just a bit of time once looking for docs on SAS.com, to no avail. Is there a whitepaper on this?

Thanks!

-Roy

-----Original Message----- From: Choate, Paul@DDS [mailto:pchoate@DDS.CA.GOV] Sent: Thursday, June 24, 2004 1:49 PM To: Pardee, Roy; SAS-L@LISTSERV.UGA.EDU 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.


Back to: Top of message | Previous page | Main SAS-L page