PHPFixing
  • Privacy Policy
  • TOS
  • Ask Question
  • Contact Us
  • Home
  • PHP
  • Programming
  • SQL Injection
  • Web3.0

Saturday, October 8, 2022

[FIXED] How to establish successful rate by interval?

 October 08, 2022     sas, sql-server, statistics     No comments   

Issue

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

enter image description here

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)
  • Share This:  
  •  Facebook
  •  Twitter
  •  Stumble
  •  Digg
Newer Post Older Post Home

0 Comments:

Post a Comment

Note: Only a member of this blog may post a comment.

Total Pageviews

Featured Post

Why Learn PHP Programming

Why Learn PHP Programming A widely-used open source scripting language PHP is one of the most popular programming languages in the world. It...

Subscribe To

Posts
Atom
Posts
Comments
Atom
Comments

Copyright © PHPFixing