05-21-2014 06:00 AM
I have a question below, how to create a table as below?
05-21-2014 06:02 AM
This is a template. It may have several branches, and I want to obtain the count(and percent of count) and sum(and percent of sum) of assets by branch*asset buckets...
05-21-2014 06:11 AM
Several ways of doing it, you could use means/freq procedures. My preference is SQL so:
/* Create empty shell */
create table WANT
/* Insert the rows */
insert into WANT
COUNT=(select count(1) from HAVE where ASSET_BUCKET="x<100"),
PERC_COUNT=strip(put((select count(1) from HAVE where ASSET_BUCKET="x<100") / whatever total is * 100,best.)||"%",
05-21-2014 06:37 AM
Try this using proc tabulate;
proc tabulate data=have;
class branch asset_buckets;
branch*(asset_buckets all), Asset*(n='Count'*f=8.0 pctn<asset_buckets all>='Perc_Count'
sum='Tot_Asset'*f=8.0 pctsum<asset_buckets all>='Perc_Tot_Asset');
05-22-2014 01:13 PM
You don't say what type of variable your ASSET_BUCKET is. If it appears as a numeric in the range 0 to some max value you probably want a format to group and control appearance.
0 -< 100 = ' x < 100'
100 -<500= '100<=x<500'
500 -<1000 = '500<=x<1000'
1000-high = 'x>=1000'
You may also what a different format for the PCT calls as by default you'll likely get 2 decimals and no % sign.
Associate that format with the asset_buckets variabl with a format statement in proc tabulate.
You may need a separate class statment to set order=internal.