通过SQL计算一列中的值 - 使用SAS

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

我有一个customer_no和class。我想为每个客户计算同一列的比率。

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

2 个解决方案

#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

SQL小提琴演示

#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;

#1