count the values in one column by SQL - using SAS

问题:

I have a customer_no and class. I would like to calculate ratio from the same column for each customer.

ratio= (number of secured/ total number classes)

customer_no        class  
1                  unsecured   
1                  secured   
1                  secured   
2                  unsecured   
2                  secured   
3                  secured   
3                  unsecured   
3                  secured   
3                  unsecured   

The output sample will be

customer_no   ratio   
1              0.666 
2              0.50  
3              0.50 
.       
.   
.  
20000      

回答1:

You can calculate ratio in a query like this:

select customer_no,
    (count(case when class = 'secured' then 1 end)     -- count if class is secured
    +0.0)                                              -- add by a double value to cast values to double to get scales
    / count(*) ratio                                   -- count of all class
from yourTable
group by customer_no;

SQL Fiddle Demo

回答2:

proc sql;
   create table want as  
   select customer_no   
   ,sum(case when class='secured' then 1 else 0 end)/count(customer_no) as ratio   
   from  WORK.ENQUIRY_30   
   group by customer_no;   
quit;