Proc tabulate

[Kitty_Zhu]

Hi guys,

I have a question below, how to create a table as below?

Branch	Asset buckets	Count	Perc_Count	Tot_Asset	Perc_Tot_Asset
123	x<100	23	17%	100000	0%
	100<=x<500	12	9%	3000002	9%
	500<=x<1000	43	31%	6223444	18%
	x>=1000	61	44%	25777532	73%
	total	139	100%	35100978	100%

Thanks!!!

[Kitty_Zhu]

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

[RW9]

Hi,

Several ways of doing it, you could use means/freq procedures.  My preference is SQL so:

proc sql;

     /* Create empty shell */

     create table WANT

     (

          BRANCH char(200),

          ASSET_BUCKETS char(200),

          COUNT num,

          PERC_COUNT char(200)

     ...);

     /* Insert the rows */

     insert into WANT

     set     BRANCH="123",

               ASSET_BUCKETS="x<100",

               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.)||"%",

               ...;

quit;

[stat_sas]

Hello,

Try this using proc tabulate;

proc tabulate data=have;

class branch asset_buckets;

var Asset;

keylabel all='Total';

table
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');

run;

[ballardw]

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.

Proc format;

value Asset_bucket

0 -< 100 = '   x < 100'

100 -<500= '100<=x<500'

500 -<1000 = '500<=x<1000'

1000-high = 'x>=1000'

run;

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.