Date: Wed, 19 Apr 2006 08:21:21 -0700
Reply-To: "Choate, Paul@DDS" <pchoate@DDS.CA.GOV>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: "Choate, Paul@DDS" <pchoate@DDS.CA.GOV>
Subject: Re: data step vs Proc sql
Content-Type: text/plain; charset="us-ascii"
Just a clarification for anyone who hasn't used the v9 colon integer
list within the IN operator - in the non-working When statement below
the put strings say "is small" and "is large". Maybe "is small digit"
would have been better.
The expression "if x in (1:5)" selects the digits 1, 2, 3, 4, & 5, not
the interval 1-5. So "where x between 1 and 5" and "if x in (1:5)"
produce different results.
Regards -
Paul Choate
DDS Data Extraction
(916) 654-2160
> -----Original Message-----
> From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of
Ed
> Heaton
> Sent: Wednesday, April 19, 2006 7:43 AM
> To: SAS-L@LISTSERV.UGA.EDU
> Subject: 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%';
| 
