How to achieve Product penetration through Group by?

[bbb_NG]

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 penetration

from 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.

[bbb_NG]

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.

[Ksharp · Accepted solution]

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

[bbb_NG]

Ksharp,

Thanks for your  prompt and helpful reply.Very thanks.