[FIXED] How to establish successful rate by interval?

Issue

I have a data like this: state of projects - number of state's projects - interval

Is it possible to establish successful rate by interval? For example: We are adding successful project states (count_n where states=successful) and divide it by the whole number of projects (sum of count_n where interval is 1-10) between 1-10 interval. The same we do with interval 1-20. I would like to get data like this: successful rate | interval X 1-10 Y 10-20

I'm coding in SAS but I can use SQL Server in it. Thanks.

Solution

In PROC SQL, you can do it this way:

proc sql;
    create table want as
        select interval
             , sum( (upcase(state) = 'SUCCESSFUL')*count_n)/sum(count_n) format=percent8.1 as success_rate
        from have
        group by interval
    ;
quit;

The code (upcase(state) = 'SUCCESSFUL') produces a 1/0 value such that only rows where the state is successful are summed. Multiplying this by count_n will give 0 for non-successful states and count_n for successful states. This is a shortcut that prevents you from having to do multiple joins to get the required numerator.

Example code:

data have;
    length state $20.;
    input state$ count_n interval$;
    datalines;
successful 70 1-10
successful 10 1-10
fail 20 1-10
successful 70 11-20
successful 5 11-20
fail 25 11-20
;
run;

Output:

interval    success_rate
1-10        80.0%
11-20       75.0%

Answered By - Stu Sztukowski

Answer Checked By - Robin (PHPFixing Admin)