Date:         Sat, 12 Aug 2000 19:41:50 +1200
Reply-To:     Don Stanley <don_stanley@XTRA.CO.NZ>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Don Stanley <don_stanley@XTRA.CO.NZ>
Subject:      Re: WHERE clause and PUT function -- inefficient comapred to IF
Comments: To: "Bruce F. Gilsen" <m1bfg00@NEWFED.FRB.GOV>
Content-Type: text/plain; charset=us-ascii

The reply from SAS Inst came throught yesterday as follows::


=========================================
Hi Don,

In version 6, the behaviour you found is because the WHERE statement is parsed by a
subsystem
separate from the DATA step.  An IF statement is parsed by the DATA step which actually
contains the
PUT function as a built-in function, which is why you get the performance difference.

This behaviour has been corrected in version 8.

Cheers

Michael Dixon
Technical Specialist
SAS Technical Support - South Pacific Region
===========================================

Don

"Bruce F. Gilsen" wrote:

> I recall this being discussed at one or more SUGI conferences some
> years ago.  My understanding is that this is not specific to the
> PUT function, but that generally, DATA step functions can make
> WHERE statements less efficient than IF statements in some cases.
>
> Bruce Gilsen gilsenb@frb.gov
>
> In article <399123B6.6787C6D4@xtra.co.nz>, don_stanley@XTRA.CO.NZ (Don Stanley) writes:
> > This might be useful .. esp if you're charged by CPU.
> >
> > Be wary of using the dataset option WHERE=,  having a PUT function in
> > the WHERE, and using a user defined format. It can add a lot of extra
> > CPU time to your step.
> >
> > Found this today under MVS 609E. Reading 6 million records as follows::
> >
> > set x (where=(put(prod,$select.) eq 'Y') ;
> >
> > took 210 seconds CPU. Changing this to
> >
> > set x ;
> > if put(prod,$select.) eq 'Y' ;
> >
> > took 130 CPU seconds.
> >
> > We also noted this with a SAS dataset on tape, reducing CPU time from
> > 100 seconds to 20 by swapping from the WHERE to an IF.
> >
> > I reported this to SAS, nothing obvious on usage notes -- one hit on
> > WHERE being less efficient than IF related to short observation widths
> > and is not our situation. I'll pass on the tech support response when it
> > arrives.
> >
> > Haven't yet checked if a problem with other functions, WHERE clauses
> > outside dataset option, formats other than user-defined etc etc.
> >
> > Don
> >
> > --
> > Don Stanley, B.SC, Dip O.R.S, MNZCS     Director, Sysware Consulting
> > Group
> >                                         Box 634, Wellington, NEW ZEALAND
> >
> >                                         http://www.sysware.co.nz
> > EMAIL:: don_stanley@xtra.co.nz
> >
> > Author:: Beyond the obvious with SAS Screen Control Language.
> > Author:: Solutions for your GUI Applications Development Using SAS/AF
> > FRAME Technology
> >
> > http://www.geocities.com/don_stanley_nz/don_home.htm
> > Genealogy:: http://www.geocities.com/don_stanley_nz/family.htm
> > SAS Tips:: http://www.geocities.com/don_stanley_nz/sastips.htm

--
Don Stanley, B.SC, Dip O.R.S, MNZCS     Director, Sysware Consulting Group
                                        Box 634, Wellington, NEW ZEALAND
                                        http://www.sysware.co.nz
EMAIL:: don_stanley@xtra.co.nz

Author:: Beyond the obvious with SAS Screen Control Language.
Author:: Solutions for your GUI Applications Development Using SAS/AF FRAME Technology

http://www.geocities.com/don_stanley_nz/don_home.htm
Genealogy:: http://www.geocities.com/don_stanley_nz/family.htm
SAS Tips:: http://www.geocities.com/don_stanley_nz/sastips.htm