Urgent question: Splitting into categories is wrong.... 1

[irinnew]

Hi everybody,

I am trying to calculate count of members according to the certain categories.
In other words I need to know how many distinct patients are under certain categories:

Medcat= “$1000”
Medcat=”$1000-2000”
Medcat=”$2000-5000”

I used proc SQL:

Proc SQL ;
Create Table Out.MedCatHmoDol As
Select MedCat , Count( Distinct Member) as NMember
From HmoDolMed
Group By MedCat ;
Quit ;

However the results of counting by categories (Medcat) makes me think that assigning of categories is wrong.
That how I assigned medcat before I used PROC SQL above:

data HMOdolmed;
set HMOdolmed;
if allow-amt<500 then medcat='<$500';
else if 500<=allow_amt<1000 then medcat='$500-$999';
else if 1000<=allow_amt<2000 then medcat='$1000-$1,999';
run;

I have a feeling that something is wrong with my splitting into categories and I wonder if the decimal of allow_amt (for ex. it looks like numeric ****.25) could cause a wrong calculation results (I mean I summary and get more than total). How can I avoid this kind error? How can I split it out not to lose anything? I have beaten my head trying to resolve it…Probably I should use Round function. How can I use it?
Any help would greately appreciated

Irin

 

[klaz2002]

Why not use proc Freq? This is what they wrote it for. See SAS help for complete documentation on the Proc Freq.
Klaz

 

[irinnew]

Klaz, don't you think that my problem is in the way I assign medcat rather then in chosing PROC SQL instead PROC FREQ?

i mean..it does work inproperly because categories (medcat) are inproperly assigned in my data step. Probably because of decimal outliers...

 

[irinnew]

Klaz,
Considering Proc Freq....how can I implement it for DISTINCT members?

 

[ChrisW75]

Safest way, and the way I generally do this sort of thing, is to set up a format on your allow_amt.
BTW, there's a typo in your code
if allow-amt<500 then medcat='<$500';
should be
if allow_amt<500 then medcat='<$500';

Anyway, this way works well:-

proc format;
  value grpfmt
    low-<500   = 'Low'
    500-<1000  = 'Medium'
    1000-<2000 = 'High'
    2000-HIGH  = 'Too high'
    ;
run;

proc freq data=HmoDolMed;
  table allow_amt /missing;
  format allow_amt grpfmt.;
run;

When doing counts, it's always a good idea to include the ones outside your range, so you have an idea how many there are there. This helps avoid issues where there are gaps in your ranges. The other thing about this method is that Proc Format will fail if the ranges set overlap in anyway, which is a good thing.