Date:         Fri, 14 Apr 2006 17:25:50 -0400
Reply-To:     "Howard Schreier <hs AT dc-sug DOT org>" <nospam@HOWLES.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Howard Schreier <hs AT dc-sug DOT org>" <nospam@HOWLES.COM>
Subject:      Re: data step vs Proc sql

On Fri, 14 Apr 2006 14:55:34 EDT, Ken Borowiak <EvilPettingZoo97@AOL.COM>
wrote:

>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.

Actually, I think it's allowed in any SQL expression. Here is an example
using LIKE in 3 contexts other than those which Ken mentioned:

    select name,
           name like 'R%' as like_R,
           max(height) as maxheight
     from sashelp.class
     group by name like 'J%'
     order by name like 'A%', name;

Result:

   Name        like_R  maxheight

   Barbara          0         72
   Carol            0         72
   Henry            0         72
   James            0       64.3
   Jane             0       64.3
   Janet            0       64.3
   Jeffrey          0       64.3
   John             0       64.3
   Joyce            0       64.3
   Judy             0       64.3
   Louise           0         72
   Mary             0         72
   Philip           0         72
   Robert           1         72
   Ronald           1         72
   Thomas           0         72
   William          0         72
   Alfred           0         72
   Alice            0         72

Can anybody find an SQL contest where the LIKE operator would make sense
but is not allowed?

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