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>
>To generalize a bit, the LIKE operator is valid in WHERE clauses (DATA
>PROCs SQL, SORT, etc.) and the CASE statement of SQL, but is not valid in
>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:
name like 'R%' as like_R,
max(height) as maxheight
group by name like 'J%'
order by name like 'A%', name;
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
>IF-THEN-ELSE. The disadvantage is that you have to learn the syntax of
>difficult-at-first-but-very-cool regular expressions.
>In a message dated 4/14/2006 12:32:15 PM Eastern Standard Time,
>On Fri, 14 Apr 2006 11:39:18 -0400, Ran S <raan67@YAHOO.COM> wrote:
>>I would like to know what is equivalent to Proc sql's 'like' operator in
>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 ;
>NOTE: There were 3 observations read from the data set SASHELP.CLASS.
> WHERE UPCASE(name) like 'JA%';