Help using Base SAS procedures

Proc tabulate

Reply
New Contributor
Posts: 4

Proc tabulate

Hi guys,

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

BranchAsset bucketsCountPerc_CountTot_AssetPerc_Tot_Asset
123x<1002317%1000000%
100<=x<500129%30000029%
500<=x<10004331%622344418%
x>=10006144%2577753273%
total139100%35100978100%

Thanks!!!

New Contributor
Posts: 4

Re: Proc tabulate

Posted in reply to 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...

Super User
Super User
Posts: 7,942

Re: Proc tabulate

Posted in reply to Kitty_Zhu

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;

Trusted Advisor
Posts: 1,228

Re: Proc tabulate

Posted in reply to Kitty_Zhu

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;

Super User
Posts: 11,338

Re: Proc tabulate

Posted in reply to Kitty_Zhu

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.

Ask a Question
Discussion stats
  • 4 replies
  • 296 views
  • 0 likes
  • 4 in conversation