topic How to achieve Product penetration through Group by? in SAS Procedures

How to achieve Product penetration through Group by?

bbb_NG — Tue, 14 Feb 2012 07:02:22 GMT

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.

How to achieve Product penetration through Group by?

bbb_NG — Tue, 14 Feb 2012 07:12:12 GMT

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.

How to achieve Product penetration through Group by?

Ksharp — Tue, 14 Feb 2012 09:27:22 GMT

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

How to achieve Product penetration through Group by?

bbb_NG — Tue, 14 Feb 2012 10:05:57 GMT

Ksharp,

Thanks for your prompt and helpful reply.Very thanks.