|
Hello,all,
I am trying to do following tasks:
1.group club_member,mth by month, get percentage which is
sum(mth)/count(distinct member_id);
2.get percentage of total sum(mth)/count(distinct member_id)as
perc_mth
for example:
month members mth perc_mth
Oct2001 10 1 10%
Nov2001 20 10 50%
Feb2002 30 3 10%
Apr2002 40 4 10%
Total 100 18 18%
I did following less efficient code for huge datasets. It took me
hours to get results. Any input for improve efficiency is greatly
appreciated.
Best wishes for the new year.
Amy.
data temp2;
set temp1;
mth=0;
if ( some condition...........) then mth=1;
expiry_month1=trim(put(put(expiry_month, YYMMn6. ), $6.));
run;
proc sql;
create table temp1 as
select
expiry_month1
,count(distinct member_id) as num
,sum(mth) as mth
,sum(mth)/count(distinct member_id)as perc_mth
from temp2
group by expiry_month1;
quit;
proc sql;
create table temp3 as
select 'Total' as expiry_month1
,count(distinct member_id) as num
,sum(mth) as mth
,sum(mth)/count(distinct member_id)as perc_mth
from temp2
quit;
data final;
set temp1 temp3;
run;
| 
