Date:         Wed, 19 Apr 2006 10:43:09 -0400
Reply-To:     Ed Heaton <EdHeaton@WESTAT.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Ed Heaton <EdHeaton@WESTAT.COM>
Subject:      Re: data step vs Proc sql
Comments: To: "Dorfman, Paul" <paul.dorfman@FCSO.COM>
Content-Type: text/plain; charset="US-ASCII"

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%';