turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- SAS Procedures
- /
- Little help with between

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

05-02-2012 01:21 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

05-02-2012 01:34 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

05-02-2012 01:28 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

05-02-2012 01:34 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

05-02-2012 03:06 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

05-02-2012 03:46 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

05-02-2012 01:38 PM

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;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

05-02-2012 01:48 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

05-02-2012 01:54 PM

Hi Jason,

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

05-02-2012 01:55 PM

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