Help using Base SAS procedures

SAS Calc code not working quite right

Accepted Solution Solved
Reply
Regular Contributor
Regular Contributor
Posts: 238
Accepted Solution

SAS Calc code not working quite right

I have this as my code:

proc sql;

create table implants.implants_sum2 as

(select distinct

sys_drg_cd,

drg_desc,

count(distinct claimnum) as Total_Claims,

sum(alloc_allw_amt) as Total_Allowed,

sum(epd_calc_allw_amt) as Total_EPD

from implants.implants_sum

where epd_calc_allw_amt= alloc_allw_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))

group by sys_drg_cd, drg_desc);

run;

It is only supposed to return any alloc_allw_amt that is between 0.01 cent and 0.05 cents compared to the epd_calc_allw_amt

so if the column looks like the below a X is what should be returned based on code above:

alloc_allw_amt                          epd_calc_allw_amt                          check

1010.00                                         1010.00                                        X

1010.05                                         1010.00                                        X

1010.10                                         1010.00                      

1010.01                                         1010.00                                        X

1010.02                                         1010.00                                        X

1010.06                                         1010.02                                        X

1010.09                                         1010.20

1010.01                                         1010.00                                        X

The problem is I am getting all those amounts you see in alloc_allw_amt column returned. It should not be returning the .10 and the .11 cent differing ones.


Accepted Solutions
Solution
‎05-07-2012 10:48 AM
Contributor
Posts: 44

Re: SAS Calc code not working quite right

select distinct 

sys_drg_cd,

drg_desc,

count(distinct claimnum) as Total_Claims,

...i'm curious as to what the red DISTINCT is intended to do that the blue one does not and that the GROUP BY cannot do.

I'm not sure we can answer the question without seeing some values for the other two columns mentioned.

View solution in original post


All Replies
Contributor
Posts: 44

Re: SAS Calc code not working quite right

I'm not sure what values you are using for sys_drg_cd and drg_desc, but you wouldn't need a DISTINCT clause when you are also using a GROUP BY.  I don't think you need the DISTINCT option where you are selecting columns in the SELECT part.

Regular Contributor
Regular Contributor
Posts: 238

Re: SAS Calc code not working quite right

I have to have the sys_drg_cd and sys_drg_desc

This is how I am group by and must have the group by these.

Contributor
Posts: 44

Re: SAS Calc code not working quite right

You can keep those two columns by using the GROUP BY, you don't need the DISTINCT option.

Your example data doesn't show what values those two columns have, the DISTINCT option may be causing the issue.

Regular Contributor
Regular Contributor
Posts: 238

Re: SAS Calc code not working quite right

Without the distinct it does not distinctly count my claimnums. If I take it out for example it will group the sys_drg_cd that is 0001 as 479 distinct claim numbers. By putting in distinct it does the correct claim count of 173 and sum the monetary columns as it should. All I need is to have the between stuff work right. That is what the problem is.

Respected Advisor
Posts: 3,124

Re: SAS Calc code not working quite right

This is indeed puzzling. I have tried the following, and it does what it supposedly do.

data have;

infile cards truncover;

input alloc_allw_amt epd_calc_allw_amt check$;

cards;

1010.00 1010.00 X

1010.05 1010.00 X

1010.10 1010.00 

1010.01 1010.00 X

1010.02 1010.00 X

1010.06 1010.02 X

1010.09 1010.20

1010.01 1010.00 X

;

proc sql;

create table want as

(select *

from have

where epd_calc_allw_amt= alloc_allw_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))

);

run;

proc print;run;

And I don't think 'group by' will change the behavior, as 'where' takes effect before 'group by'. I know it may sound highly unlikely, but what is the length of your numeric variables? If it is much less than the default '8', then precision could be the issue. Also, what is your running platform?

Regards,

Haikuo

Solution
‎05-07-2012 10:48 AM
Contributor
Posts: 44

Re: SAS Calc code not working quite right

select distinct 

sys_drg_cd,

drg_desc,

count(distinct claimnum) as Total_Claims,

...i'm curious as to what the red DISTINCT is intended to do that the blue one does not and that the GROUP BY cannot do.

I'm not sure we can answer the question without seeing some values for the other two columns mentioned.

Regular Contributor
Regular Contributor
Posts: 238

Re: SAS Calc code not working quite right

Ah that distinct. I took that out and got the correct claim counts now. I also think that the reason I am confused on the monetary is because when it sums it up by the sys_drg_cd or groups by I should say, it takes all DRG's that are 0001 which is a heart transplant and totals those claims and then I get the sum of the alloc_amt and epd_amt which is not within the 0.01 or 0.05 cents however I am asking for each claim to be within that monetary amount, not the final sum of information. So I can see why some of these are off by more than 0.05 cents.


Contributor
Posts: 44

Re: SAS Calc code not working quite right

I'm glad you got the first part working...I'm not sure I understand the issue with the second part. I might suggest trying to break it into two separate steps if you are truly trying to do two separate things in one step. It may be easier to maintain/understand your own code.

PROC Star
Posts: 7,366

Re: SAS Calc code not working quite right

It would help if you provided a sample input data and, while it probably won't affect your result, you should end your proc sql code with a quit rather than a run statement.

With the following data, you would get the desired result, using YOUR current code:

data implants.implants_sum;

  input sys_drg_cd drg_desc claimnum alloc_allw_amt  epd_calc_allw_amt;

  cards;

1 1 1 1010.00  1010.00

2 2 2 1010.05  1010.00

3 3 3 1010.10  1010.00

4 4 4 1010.01  1010.00

5 5 5 1010.02  1010.00

6 6 6 1010.06  1010.02

7 7 7 1010.09  1010.20

8 8 8 1010.01  1010.00

;

proc sql;

create table implants.implants_sum2 as

(select distinct

sys_drg_cd,

drg_desc,

count(distinct claimnum) as Total_Claims,

sum(alloc_allw_amt) as Total_Allowed,

sum(epd_calc_allw_amt) as Total_EPD

from implants.implants_sum

where epd_calc_allw_amt= alloc_allw_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))

group by sys_drg_cd, drg_desc);

quit;

Super User
Posts: 10,550

Re: SAS Calc code not working quite right

I suspect ou are getting the unwanted values because you are asking for them here:

where epd_calc_allw_amt= alloc_allw_amt or <other stuff>

if the above 2 variables have the objectionable value 1010.10 they are accepted as this part of the selection criteria is true.

🔒 This topic is solved and locked.

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

Discussion stats
  • 10 replies
  • 285 views
  • 0 likes
  • 5 in conversation