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)
0 Comments:
Post a Comment
Note: Only a member of this blog may post a comment.