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
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;
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;
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;
I agree, @Astounding, PROC FREQ would be my first choice too in this situation 🙂
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.