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;
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))
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
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))
You are a genius. This works perfectly. ABS did not return correctly but what you provided is perfect.
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
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;
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.
Hi Jason,
You are right. Hope the OP doesn't have any value in that range. - Linlin
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.