BookmarkSubscribeRSS Feed
Kitty_Zhu
Calcite | Level 5

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

4 REPLIES 4
Kitty_Zhu
Calcite | Level 5

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Ammonite | Level 13

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
Super User

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 787 views
  • 0 likes
  • 4 in conversation