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!!!
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...
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;
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;
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.