BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
tmm
Fluorite | Level 6 tmm
Fluorite | Level 6

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;

1 ACCEPTED SOLUTION

Accepted Solutions
JasonDiVirgilio
Quartz | Level 8

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

View solution in original post

8 REPLIES 8
Reeza
Super User

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

JasonDiVirgilio
Quartz | Level 8

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

tmm
Fluorite | Level 6 tmm
Fluorite | Level 6

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

Reeza
Super User

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 Smiley Happy

Linlin
Lapis Lazuli | Level 10

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;

JasonDiVirgilio
Quartz | Level 8

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.


Linlin
Lapis Lazuli | Level 10

Hi Jason,

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

Jay_TxOAG
Quartz | Level 8

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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