topic Re: Proc tabulate in SAS Procedures

Proc tabulate

Kitty_Zhu — Wed, 21 May 2014 10:00:13 GMT

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

Re: Proc tabulate

Kitty_Zhu — Wed, 21 May 2014 10:02:18 GMT

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

Re: Proc tabulate

RW9 — Wed, 21 May 2014 10:11:56 GMT

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;

Re: Proc tabulate

stat_sas — Wed, 21 May 2014 10:37:03 GMT

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;

Re: Proc tabulate

ballardw — Thu, 22 May 2014 17:13:58 GMT

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.