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.
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.