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 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()?
Comments: To: pardee.r@GHC.ORG
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.


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