Date:         Tue, 30 Mar 2010 04:01:20 -0400
Reply-To:     Peter Crawford <Peter.Crawford@BLUEYONDER.CO.UK>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Peter Crawford <Peter.Crawford@BLUEYONDER.CO.UK>
Subject:      Re: between-and operator

On Mon, 29 Mar 2010 17:09:04 -0400, Mike Zdeb <msz03@ALBANY.EDU> wrote:

>hi ... it's not PROC versus data step that determines when you can use
BETWEEN, it's
>WHERE versus IF ... whether you are asking questions about variables in
an already existing
>data set or are about new variables (though WHERE CALCULATED and HAVING
give you some leeway in SQL)
>
>so, if you're asking questions about variables already in a data set, you
can
>use WHERE and BETWEEN in a data step ...
>
>data new;
>set old;
>where myvar between '120.0' and '120.9';
>run;
>
>with ICD codes, it's often the first three characters that define a major
subgroup
>for example, ASTHMA could be coded as
>493.00 493.01 493.02 493.10 493.11 493.12 493.20 493.21 493.22 493.81
493.82 493.90 493.91 493.92
>
>so something like this works great ...
>
>data asthma;
>set old;
>where myvar eq : '493';
>run;
>
>if you try that in PROC SQL, EQ : does not work, but EQT does ...
>
>proc sql;
>create table asthmna as
>select * from old
>where myvar eqt '493';
>quit;
>
>the colons don't work with BETWEEN, but you can still use BETWEEN when
variable
>length are longer than the strings you specify
>for example, ICD codes associated with childbirth range from  all codes
that start with '630'
>to all those that start with '676'
>
>if the ICD codes are character variables with a length greater than 3,
you can use
>
>data childbirth;
>set old;
>where put(icd,$3.) between '630' and '676';
>run;
>
>or as in the original posting, you mentioned 560.3-560.9
>I think those would most likely be length 6 given that the ICD codes are
>
>560.30 IMPACTION OF INTESTINE, UNSPECIFIED
>560.31 GALLSTONE ILEUS
>560.39 OTHER IMPACTION OF INTESTINE
>560.81 INTESTINAL OR PERITONEAL ADHESIONS WITH OBSTRUCTION (POSTOPERATIVE)
>560.89 OTHER SPECIFIED INTESTINAL OBSTRUCTION
>560.9  UNSPECIFIED INTESTINAL OBSTRUCTION
>
>so ...
>
>data feeling_cramped;
>set old;
>where put(icd,$5.) between '560.3' and '560.9';
>run;
>
>
>--
>Mike Zdeb
>U@Albany School of Public Health
>One University Place
>Rensselaer, New York 12144-3456
>P/518-402-6479 F/630-604-1475
>
>> Hi Peichang,
>>
>>
>> As you mention, in SQL you can use:
>>      myvar between 100 and 1000
>> or
>>      myvar between '120.0' and '120.9'
>>
>>
>> In the datastep you can use:
>>      100 le myvar le 1000
>> or
>>      '120.0' le myvar le '120.9'
>>
>>
>>
>>
>> Hope this is helpful.
>>
>>
>> Mark Terjeson
>> Investment Business Intelligence
>> Investment Management & Research
>> Russell Investments
>> 253-439-2367
>>
>>
>> Russell
>> Global Leaders in Multi-Manager Investing
>>
>
>> -----Original Message-----
>> From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of PC
>> Sent: Monday, March 29, 2010 8:29 AM
>> To: SAS-L@LISTSERV.UGA.EDU
>> Subject: between-and operator
>>
>> I know for numeric values, you can specify condition like, between 100
>> and 1000; in SQL , you can use character , such as between '120.0' and
>> '120.9';
>> Is there any way to do that in SAS which will be very useful?
>> I am asking this, because for ICD9 code, some diseases are defined as
>> 560.3-560.9, there are also some minor codes, such as 560.41 etc. I
>> can not list all of them using icd9 in (1,2....)
>>
>> Thanks
>> Peichang
>>

one issue to consider with WHERE handling of BEWTEEN is index-use.
see http://tinyurl.com/index-when (a short form of
http://support.sas.com/documentation/cdl/en/lrcon/61722/HTML/default/a00044
0261.htm#a002591426 )

Or see the book on indexes by MIIIIIKKKKEEEEE at
http://www.sas.com/apps/pubscat/bookdetails.jsp?catid=1&pc=60409

I just added Account_number to an sql join clause (already supported by a
nearly unique transaction reference number) and the join execution time
fell from several minutes to sub-second, because account_number, in the
large transaction table in the join, was indexed!

Of course the data volumes in the ICD codes may be small enough that
seeking this kind of runtime performance is less important.

peterC