how to count the values from one column using SQl

Accepted Solution Solved
Reply
Contributor
Posts: 25
Accepted Solution

how to count the values from one column using SQl

[ Edited ]

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

 


Accepted Solutions
Solution
‎10-08-2017 07:24 AM
PROC Star
Posts: 1,218

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

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;

View solution in original post


All Replies
Solution
‎10-08-2017 07:24 AM
PROC Star
Posts: 1,218

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

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;
Super User
Posts: 6,642

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

I think @draycut 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;

PROC Star
Posts: 1,218

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

Posted in reply to Astounding

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 185 views
  • 1 like
  • 3 in conversation