Date: Wed, 19 Apr 2006 11:13:24 -0400
Reply-To: "Dorfman, Paul" <Paul.Dorfman@FCSO.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: "Dorfman, Paul" <Paul.Dorfman@FCSO.COM>
Subject: Re: data step vs Proc sql
Content-Type: text/plain; charset=us-ascii
Ed,
A nice catch. I would consider the lack of operatic share between IF and
WHEN an apparent blunder on SI's part. However, I am not surprised there
is stuff working in IF and not in WHERE - the colon modifier would be an
example (true, there are EQT, etc. operators in WHERE but they work
somewhat differently - Jack Hamilton has written on that in the past).
On the emotional level, my SAS skin is perhaps way too thick to be
disappointed by such a finding, let alone to be distressed or grievously
wounded :). Maybe this is because nowadays there are more ways to the
same in SAS than ever. Maybe, I am still excited from yesterday after
reading Jason Secosky abstract for the upcoming NESUG, where he is going
to talk about using FCMP in 9.2 to - finally!- create user-defined
functions to be called from the DATA step (and not only from certain
procs). To mention nothing else, there are dynamic arrays thrown in for
a good measure... and have not we asked for them for years? I guess that
produced enough endorphins yesterday to cover for the lack of in(m:n) in
WHEN clause today :).
Kind regards
------------
Paul Dorfman
Jax, FL
------------
> -----Original Message-----
> From: Ed Heaton [mailto:EdHeaton@westat.com]
> Sent: Wednesday, April 19, 2006 10:43 AM
> To: Dorfman, Paul; SAS-L@LISTSERV.UGA.EDU
> Subject: RE: Re: data step vs Proc sql
>
> Paul,
>
> Since we are on the topic of WHEN statements...
>
> I was disappointed to learn recently that the following code
> would not work.
>
> Data test ;
> Do x=1 to 15 by 0.5 ;
> Output ;
> End ;
> Run ;
> Data new ;
> Set test ;
> If ( x in (1:10) ) ;
> Select (x) ;
> When (1:5) put x= "is small." ;
> Otherwise put x= "is large." ;
> End ;
> Run ;
>
> This bombs on the WHEN statement. The following DATA step
> subsets just fine.
>
> Data new ;
> Set test ;
> If ( x in (1:10) ) ;
> Run ;
>
> Now, the WHERE statement doesn't process the integer list but
> rather it returns an error.
>
> Data new ;
> Set test ;
> Where ( x in (1:10) ) ;
> Run ;
>
> So, here is a case where an expression works in the IF
> statement but doesn't work in the WHERE or WHEN statement.
>
>
> Ed
>
> Edward Heaton, SAS Senior Systems Analyst, Westat (An
> Employee-Owned Research Corporation), 1600 Research
> Boulevard, RW-4541, Rockville, MD 20850-3195
> Voice: (301) 610-4818 Fax: (301) 294-3879
> mailto:EdHeaton@Westat.com http://www.Westat.com
>
>
>
>
> -----Original Message-----
> From: Dorfman, Paul [mailto:paul.dorfman@FCSO.COM]
> Sent: Wednesday, April 19, 2006 10:27 AM
> To: SAS-L@LISTSERV.UGA.EDU; Ed Heaton
> Subject: Re: data step vs Proc sql
>
>
> Ed,
>
> The clarification seem to conform to your thoroughly wholesome nature
> :). Frankly, the failure of WHEN to act like (no pun) WHERE does not
> surprise me too much, for I would instinctively expect it to
> accept the
> same set of operators as IF. After all, the case structure is nothing
> else but the beatified bunch of IF-THEN-ELSEs, or IF-GOTOs, if one
> should prefer.
>
> That is not to say that I would not like IF/WHEN to be able to accept
> all operators not available to it but available to WHERE, and
> maybe even
> the other way around as well, which would be the best of both worlds -
> for if all operators were shared, we would not have to dwell on which
> are applicable where, and it would likely make SAS code more robust.
>
> However, while I am pretty sure all WHERE operators can be - given the
> due diligence and sense of necessity - implemented for
> IF/WHEN, I doubt
> the reverse is true - perhaps because WHERE tends to be standardized
> with SQL in minds first; but I would not be surprised if I
> were wrong on
> this latter count.
>
> Kind regards
> ------------
> Paul Dorfman
> Jax, FL
> ------------
>
> On Wed, 19 Apr 2006 08:40:24 -0400, Ed Heaton <EdHeaton@WESTAT.COM>
> wrote:
>
> >Ken,
> >
> >Nobody responded about the SELECT block in the DATA step.
> It doesn't
> >allow the LIKE operator. To expand from Venky's example.
> >
> >NOTE: SAS (r) 9.1 (TS1M3)
> >NOTE: This session is executing on the XP_PRO platform.
> >NOTE: SAS 9.1.3 Service Pack 3
> >
> >1 Data _null_ ;
> >2 Set sasHelp.Class ;
> >3 Select ;
> >4 When ( upCase(Name) like "JA%" ) then put Name = ;
> > ----
> > 22
> > 76
> >ERROR 22-322: Syntax error, expecting one of the following:
> > !, !!, &, ), *, **, +, ',', -, /, <, <=, <>, =, >, ><,
> > >=, AND, EQ, GE, GT, IN, LE, LT, MAX, MIN, NE, NG, NL,
> > NOT, NOTIN, OR, ^, ^=, |, ||, ~, ~=.
> >
> >ERROR 76-322: Syntax error, statement will be ignored.
> >
> >5 Otherwise ;
> >6 End ;
> >7 Run ;
> >
> >NOTE: The SAS System stopped processing this step because of errors.
> >NOTE: DATA statement used (Total process time):
> > real time 0.30 seconds
> > cpu time 0.09 seconds
> >
> >
> >Ed
> >
> >Ed Heaton
> >RW-4541
> >#4818
> >
> >
> >
> >-----Original Message-----
> >From: owner-sas-l@listserv.uga.edu
> >[mailto:owner-sas-l@listserv.uga.edu]
> >On Behalf Of EvilPettingZoo97@aol.com
> >Sent: Friday, April 14, 2006 2:56 PM
> >To: swovcc@HOTMAIL.COM; SAS-L@listserv.uga.edu
> >Subject: Re: data step vs Proc sql
> >
> >
> >To generalize a bit, the LIKE operator is valid in WHERE
> clauses (DATA
> >step, PROCs SQL, SORT, etc.) and the CASE statement of SQL,
> but is not
> >valid in the IF-THEN-ELSE construct of the DATA step. Can
> someone help
>
> >me out with SELECT blocks of the DATA step?
> >
> >Mona alluded to using PRXMATCH in an earlier response, which has the
> >advantage over LIKE in that it is more flexible & can be
> used in WHERE
> >clauses *and* IF-THEN-ELSE. The disadvantage is that you
> have to learn
> >the syntax of difficult-at-first-but-very-cool regular expressions.
> >
> >Ken
> >
> >
> >In a message dated 4/14/2006 12:32:15 PM Eastern Standard Time,
> >swovcc@HOTMAIL.COM writes: On Fri, 14 Apr 2006 11:39:18 -0400, Ran S
> ><raan67@YAHOO.COM> wrote:
> >
> >>Hi experts,
> >>
> >>I would like to know what is equivalent to Proc sql's
> 'like' operator
> >>in data step?
> >>
> >>Thanks!
> >
> >It is ... well ... like "LIKE" :-).
> >
> >The same operator is valid in the data step too:
> >
> >1 data _null_ ;
> >2 set sashelp.class ;
> >3 where upcase(name) like "JA%" ;
> >4 put name = ;
> >5 run ;
> >
> >NAME=James
> >NAME=Jane
> >NAME=Janet
> >NOTE: There were 3 observations read from the data set SASHELP.CLASS.
> > WHERE UPCASE(name) like 'JA%';
>
>
>
First Coast Service Options, Inc., and its affiliates are not responsible for errors or omissions in the transmission of this e-mail message. Any personal comments made in this e-mail do not reflect the views of First Coast Service Options, Inc., or its affiliates. The information contained in this document may be confidential and is intended solely for the use of the individual or entity to whom it is addressed. This document may also contain material that is privileged or protected from disclosure under applicable law. If you are not the intended recipient or the individual responsible for delivery to the intended recipient, please (1) be advised that any use, dissemination, forwarding, or copying of this document IS STRICTLY PROHIBITED; and (2) notify sender immediately by telephone and destroy the document. Thank you.
| 
