Dear all,
I'm facing a product penetration problem.
I have a table t1
CustomerID | SavingAccount | TermDeposit | Loan |
aaa | Y | ||
bbb | Y | Y | |
ccc | Y | Y | |
ddd | Y | ||
eee | Y |
select t1.SavingAccount,count(Savingaccount) as sa, count(*) as allNo,
count(Savingaccount)/count(*) as penetrationfrom t1 group by savingaccount;
it will present
0 1 0%
4 4 100%
but what I want is
0 5 0%
4 5 80%
I want count(*) to calculate the original number,not the grouped number.
Can anyone give a hand?
Very thanks.
It looks like you need sub-query in SQL.
data have; input (CustomerID SavingAccount TermDeposit Loan) (: $40.); cards; aaa . . Y bbb Y . Y ccc Y Y . ddd Y . . eee Y . . ; run; proc sql; create table want as select sum,(select count(*) from have) as total ,sum/calculated total as percent format=percent8. from (select count(SavingAccount) as sum from have group by SavingAccount); quit;
Ksharp
What I'm trying to ask further is
that when filter/group changed how to change respectively,
what solution I present is that
when grouped by Savingaccount,
it will show as follows:
Y | 4 |
nill | 1 |
Total | 5 |
thus the penetration can be easily calculated.
It looks like you need sub-query in SQL.
data have; input (CustomerID SavingAccount TermDeposit Loan) (: $40.); cards; aaa . . Y bbb Y . Y ccc Y Y . ddd Y . . eee Y . . ; run; proc sql; create table want as select sum,(select count(*) from have) as total ,sum/calculated total as percent format=percent8. from (select count(SavingAccount) as sum from have group by SavingAccount); quit;
Ksharp
Ksharp,
Thanks for your prompt and helpful reply.Very thanks.
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 the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.