Help using Base SAS procedures

How to achieve Product penetration through Group by?

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 99
Accepted Solution

How to achieve Product penetration through Group by?

Dear all,

I'm facing a product penetration problem.

I have a table t1

CustomerIDSavingAccountTermDepositLoan
aaaY
bbbYY
cccYY
dddY
eeeY

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.


Accepted Solutions
Solution
‎02-14-2012 04:27 AM
Super User
Posts: 10,035

How to achieve Product penetration through Group by?

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

View solution in original post


All Replies
Frequent Contributor
Posts: 99

How to achieve Product penetration through Group by?

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:

Y4
nill1
Total5

thus the penetration can be easily calculated.

Solution
‎02-14-2012 04:27 AM
Super User
Posts: 10,035

How to achieve Product penetration through Group by?

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

Frequent Contributor
Posts: 99

How to achieve Product penetration through Group by?

Ksharp,

Thanks for your  prompt and helpful reply.Very thanks.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 291 views
  • 0 likes
  • 2 in conversation