Calculate percentage of multiple rows out of one row

[lizzy28]

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%

[lizzy28]

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!

[ballardw]

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;

[lizzy28]

Thank you! I wanted a separate dataset.

[novinosrin · Accepted solution]

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;

[lizzy28]

Thanks a lot!

[novinosrin]

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