how to count the values from one column using SQl

[ihlayyel]

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 
.       
.   
.  
9999      

[PeterClemmensen · Accepted solution]

like this?

data have;
input customer_no$ class$;
datalines;
1 unsecured 
1 secured 
1 secured
2 unsecured
2 secured
3 secured
3 unsecured
3 secured
3 unsecured
;

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 have
   group by customer_no;
quit;

[Astounding]

I think @PeterClemmensen gave you the right answer.  Here is just another tool that may come in handy in this situation:

proc freq data=have;

tables customer_no * class / noprint out=counts;

run;

proc print data=counts;

var customer_no percent;

where class='secured';

run;

[PeterClemmensen]

I agree, @Astounding, PROC FREQ would be my first choice too in this situation 🙂