Little help with between

Solved
Regular Contributor
Posts: 238

Little help with between

I don't think my where statements are right. I want to say if the epd is a certain amount or if it is between 1 and 5 cents higher or lower than the allw_amt that I want the information to return in the sum2 table I am creating. So example info

epd_amt                         alloc_amt

1010.00                          1010.00

1010.01                          1010.00

1010.02                          1010.00

1010.03                          1010.00

1010.04                          1010.00

1010.05                          1010.00

1010.06                          1010.00

1010.12                          1010.00

With this information the only items that should return are anything that is equal which there is 1 and the the ones that are .01 to .05 cents. The .06 and .12 should not be in my table.

proc sql;

create table sum2 as

(select distinct

drg_cd,

drg_desc,

count(claimnum) as TotalClaims,

sum(alloc_allw_amt) as TotalAllowed,

sum(epd_calc_allw_amt) as TotalEPD

from sum

where epd_calc_allw_amt = alloc_allw_amt or

epd_calc_allw_amt between + 0.01 and  + 0.05 = alloc_allw_amt or

epd_calc_allw_amt between -0.01 and -0.05 = alloc_allw_amt

group by sys_drg_cd, drg_desc);

run;

Accepted Solutions
Solution
‎05-02-2012 01:34 PM
Contributor
Posts: 46

Re: Little help with between

Try changing the WHERE clause to use:

where epd_calc_allw_amt = alloc_amt or

(epd_calc_allw_amt between (alloc_allw_amt + 0.01) and  (alloc_allw_amt + 0.05 )) or

(epd_calc_allw_amt between (alloc_allw_amt - 0.01) and  (alloc_allw_amt - 0.05))

All Replies
Super User
Posts: 23,683

Re: Little help with between

That's the same logic as saying the absolute difference between the two numbers is less than 0.05, this captures the above, below and 0.

Assuming that the rest of your code is correct the following would work. I'm not sure what your actual variable names are, the names in the example data aren't the same in your query, so you can modify accordingly.

where abs(epd_amount-alloc_amt)<0.05

Solution
‎05-02-2012 01:34 PM
Contributor
Posts: 46

Re: Little help with between

Try changing the WHERE clause to use:

where epd_calc_allw_amt = alloc_amt or

(epd_calc_allw_amt between (alloc_allw_amt + 0.01) and  (alloc_allw_amt + 0.05 )) or

(epd_calc_allw_amt between (alloc_allw_amt - 0.01) and  (alloc_allw_amt - 0.05))

Regular Contributor
Posts: 238

Re: Little help with between

Posted in reply to JasonDiVirgilio

You are a genius. This works perfectly. ABS did not return correctly but what you provided is perfect.

Super User
Posts: 23,683

Re: Little help with between

The abs didn't work because its missing the <= rather than just < but the logic is identical to Jasons.

It's good to know more than one way

Super Contributor
Posts: 1,636

Re: Little help with between

data sum;
input epd_amt      :                   alloc_amt;
cards;
1010.00                          1010.00
1010.01                          1010.00
1010.02                          1010.00
1010.03                          1010.00
1010.04                          1010.00
1010.05                          1010.00
1010.06                          1010.00
1010.12                          1010.00
;

proc sql;
create table sum2 as select *
from sum
where abs(epd_amt- alloc_amt) <=.05;
quit;
proc print;run;

Contributor
Posts: 46

Re: Little help with between

I think the ABS function could cause problems here because they don't want values in the range of 0.00 to 0.01 and 0.00 to -0.01.

Super Contributor
Posts: 1,636

Re: Little help with between

Posted in reply to JasonDiVirgilio

Hi Jason,

You are right. Hope the OP doesn't have any value in that range.  - Linlin

Contributor
Posts: 46

Re: Little help with between

just curious if this is what you intend the range to be...the other answers seem to accomplish this also

where

epd_calc_allw_amt - 0.05 <= alloc_allw_amt <= epd_calc_allw_amt + 0.05

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
• 8 replies
• 290 views
• 0 likes
• 5 in conversation