SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Calculate percentage of multiple rows out of one row

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 110
Accepted Solution

Calculate percentage of multiple rows out of one row

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%

 


Accepted Solutions
Solution
a week ago
PROC Star
Posts: 1,561

Re: Calculate percentage of multiple rows out of one row

[ Edited ]
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;

View solution in original post


All Replies
Frequent Contributor
Posts: 110

Re: Calculate percentage of multiple rows out of one row

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!

Super User
Posts: 13,295

Re: Calculate percentage of multiple rows out of one row

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;

 

Frequent Contributor
Posts: 110

Re: Calculate percentage of multiple rows out of one row

Thank you! I wanted a separate dataset.

 

Solution
a week ago
PROC Star
Posts: 1,561

Re: Calculate percentage of multiple rows out of one row

[ Edited ]
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;
Frequent Contributor
Posts: 110

Re: Calculate percentage of multiple rows out of one row

Posted in reply to novinosrin

Thanks a lot!

PROC Star
Posts: 1,561

Re: Calculate percentage of multiple rows out of one row

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 133 views
  • 0 likes
  • 3 in conversation