topic Re: Little help with between in SAS Procedures

Little help with between

tmm — Wed, 02 May 2012 17:21:00 GMT

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;

Re: Little help with between

Reeza — Wed, 02 May 2012 17:28:53 GMT

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

Re: Little help with between

JasonDiVirgilio — Wed, 02 May 2012 17:34:29 GMT

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

Re: Little help with between

Linlin — Wed, 02 May 2012 17:38:48 GMT

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;

Re: Little help with between

JasonDiVirgilio — Wed, 02 May 2012 17:48:59 GMT

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.

Re: Little help with between

Linlin — Wed, 02 May 2012 17:54:20 GMT

Hi Jason,

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

Re: Little help with between

Jay_TxOAG — Wed, 02 May 2012 17:55:27 GMT

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

Re: Little help with between

tmm — Wed, 02 May 2012 19:06:03 GMT

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

Re: Little help with between

Reeza — Wed, 02 May 2012 19:46:06 GMT

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