topic Calculate percentage of multiple rows out of one row in SAS Data Management

Calculate percentage of multiple rows out of one row

lizzy28 — Mon, 11 Jun 2018 19:40:16 GMT

Hi everyone,

I need to calculate the percentage of multiple rows out of one row (this row is the total of other rows). Can anyone help with some simple code?

The data I have is

Account	cnt_201701	cnt_201702	cnt_201703	cnt_201704	cnt_201705	cnt_201706
A	296	370	375	395	365	319
B	4549	7157	8759	9705	9355	9697
C	1687	2356	2803	3060	3281	3497
Total	6532	9883	11937	13160	13001	13513

The target data is

Account	cnt_201701	cnt_201702	cnt_201703	cnt_201704	cnt_201705	cnt_201706
A	4.5%	3.7%	3.1%	3.0%	2.8%	2.4%
B	69.6%	72.4%	73.4%	73.7%	72.0%	71.8%
C	25.8%	23.8%	23.5%	23.3%	25.2%	25.9%
Total	100.0%	100.0%	100.0%	100.0%	100.0%	100.0%

Re: Calculate percentage of multiple rows out of one row

lizzy28 — Mon, 11 Jun 2018 19:44:24 GMT

All the columns of cnt_ in both tables should be numeric. Somehow they look like character as I pasted the data here.

Thank you all!

Re: Calculate percentage of multiple rows out of one row

ballardw — Mon, 11 Jun 2018 19:45:55 GMT

Do you want a data set (further computations needed) or a report (people are going to read this?

In either case including the "total" in the existing data isn't really needed or generally desirable.

And it is very likely the desired result could be made from the data set used to create the displayed "have" data.

A report table, one way:

proc tablulate data=have;

where account ne 'Total';

class account cnt: ;

table account all='Total',

(cnt: )* colpctn=''

;

run;

Re: Calculate percentage of multiple rows out of one row

lizzy28 — Mon, 11 Jun 2018 19:49:32 GMT

Thank you! I wanted a separate dataset.

Re: Calculate percentage of multiple rows out of one row

novinosrin — Mon, 11 Jun 2018 20:16:01 GMT

data have;
input Account $	cnt_201701	cnt_201702	cnt_201703	cnt_201704	cnt_201705	cnt_201706;
cards;
A	296	370	375	395	365	319
B	4549	7157	8759	9705	9355	9697
C	1687	2356	2803	3060	3281	3497
Total	6532	9883	11937	13160	13001	13513
;

proc transpose data=have(where=(account='Total')) out=temp(drop=account);
by account;
run;

data want;
if _n_=1 then do;
if 0 then set temp;
  dcl hash H (dataset:'temp',ordered: "A") ;
   h.definekey  ("_name_") ;
   h.definedata ("col1") ;
   h.definedone () ;
end;
set have;
array t(*) cnt_:;
do _n_=1 to dim(t);
rc=h.find(key:vname(t(_n_)));
t(_n_)=t(_n_)/col1*100;
end;
drop rc _name_;
run;

Re: Calculate percentage of multiple rows out of one row

lizzy28 — Mon, 11 Jun 2018 20:22:56 GMT

Thanks a lot!

Re: Calculate percentage of multiple rows out of one row

novinosrin — Mon, 11 Jun 2018 20:24:04 GMT

@lizzy28 You are very welcome. Have a great day!