topic Re: how to count the values from one column using SQl in SAS Programming

how to count the values from one column using SQl

ihlayyel — Sun, 08 Oct 2017 10:42:54 GMT

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

Re: how to count the values from one column using SQl

PeterClemmensen — Sun, 08 Oct 2017 11:07:47 GMT

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;

Re: how to count the values from one column using SQl

Astounding — Sun, 08 Oct 2017 11:16:59 GMT

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;

Re: how to count the values from one column using SQl

PeterClemmensen — Sun, 08 Oct 2017 11:19:48 GMT

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