BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
lizzy28
Quartz | Level 8

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%

 

1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20
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

6 REPLIES 6
lizzy28
Quartz | Level 8

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

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
Quartz | Level 8

Thank you! I wanted a separate dataset.

 

novinosrin
Tourmaline | Level 20
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;
novinosrin
Tourmaline | Level 20

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

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!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

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