Calculate Percentage with rows and column calculation

Accepted Solution Solved
Reply
Contributor
Posts: 29
Accepted Solution

Calculate Percentage with rows and column calculation

i have to perform percent calculation using numerator and denominator. But to do that i have to calculate Numerator and Denominator first,

 

Sample Data

 

YrQtr           Metric    Month03  Month06 Month09 Month12 .......................Month60

2012Q3       deltaA        11           25            36          46                                  

2012Q3       deltaB        15           40            36         24  

2012Q3       deltaC        23           45            34         45

2012Q3       deltaD        23           33             44         76

2012Q4       deltaA        13           27            38          47                                  

2012Q4       deltaB        18           42            38         28  

2012Q4       deltaC        26           47            36         48

2012Q4       deltaD        28           36             46         78

;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;

......................................................................................

2016Q1      DeltaA         4           15               23         .

 

So to calculate the numerator i have Logic as

if month06.DeltaA <0 then 0 else Month06.DeltaA=Month06.DeltaA-Month03.DeltaA

if month09.DeltaA <0 then 0 else Month09.DeltaA=Month09.DeltaA-Month06.DeltaA

 

So My Numerator after subtraction should look like below

 

YrQtr           Metric     Month06   Month09        Month12 .......................Month60

2012Q3       deltaA     25-11            36-25          46-36                                  

 

2012Q4       deltaA      27-13            38-27          47-38                                  

 

  .

 

To Calculate Denominator its bit More complicated

 

IF 2012Q4.DeltaC,Month03 <> .[null] then

 2012Q3.DeltaD,Month03-(2012Q3.DeltaB,Month03+2012Q3.DeltaA,Month03)+2012Q3.DeltaC,Month06

 

So it will be like 28 <>[Null] then 23-(15+11)+45      

Denominator for Month06 will be 42

36 <>[null] then 33-(40+25)+34

Denominator for Month06 will be 2

 

YrQtr           Metric              Month06      Month09    Month12 .......................Month60

2012Q3       Denominator      42                    2                               

 

 

 Percentage will 

 

YrQtr           Metric              Month06                    Month09                   Month12 .......................Month60

2012Q3      Percentage         25-11/42                   36-25/ 2                               

 


Accepted Solutions
Solution
‎06-20-2016 10:04 AM
PROC Star
Posts: 1,671

Re: Calculate Percentage with rows and column calculation

Like this?

 

 

do I=2 to 5;
 NUM[I] = (MONTH[I]>0) * (MONTH[I]-MONTH[I-1]);
end;
do I=1 to 4;
  DEN[I] = (MONTH[I]>0) * (lag5(MONTH[I]) - lag6(MONTH[I]) - lag7(MONTH[I]) + lag5(MONTH[I+1]) ) ;
end;

View solution in original post


All Replies
Super User
Posts: 19,038

Re: Calculate Percentage with rows and column calculation

I didn't follow all your calculations, but the numerator looks to be dealing with only one row at a time?

 

If so, I think an array is what you need.

 

data want;
set have;

array months(*) Month:;
array num(*) Num_Month06_Delta Num_Month09_Delta ... Num_Month60_Delta;
array den(*) DEN_Month03 Den Month06 Den_Month09 .. Den_Month60;

do i=1 to dim(num);
/*Numerator*/
if months(i+1)<0 then num(i)=0; 
else num(i) = months(i+1)-months(i);

end;
run;

For the denominator what does the following mean? Specifically, the second part of the formula in brackets has comma's between the values but I don't know what that means.

IF 2012Q4.DeltaC,Month03 <> .[null] then 2012Q3.DeltaD,Month03-(2012Q3.DeltaB,Month03+2012Q3.DeltaA,Month03)+2012Q3.DeltaC,Month06
Contributor
Posts: 29

Re: Calculate Percentage with rows and column calculation

Hi Reeza,

                     Thanks for your reply on numerator. Yes you are correct about calculation of numerator being done on same row.

Sorry about commas in denominator calculation it should be full stop. 

 

IF 2012Q4.DeltaC.Month03 <> .[null] then 2012Q3.DeltaD.Month03-(2012Q3.DeltaB.Month03+2012Q3.DeltaA.Month03)+2012Q3.DeltaC.Month06.

 

So denominator for month06 translates to below

28 <>[Null] then 23-(15+11)+45

 

 

PROC Star
Posts: 1,671

Re: Calculate Percentage with rows and column calculation

Like this?

 

 

data HAVE;

input YRQTR $ METRIC $ MONTH03 MONTH06 MONTH09 MONTH12 MONTH15;

cards;

2012Q3 deltaA 11 25 36 46 36

2012Q3 deltaB 15 40 36 24 36

2012Q3 deltaC 23 45 34 45 34

2012Q3 deltaD 23 33 44 76 44

2012Q4 deltaA 13 27 38 47 38

2012Q4 deltaB 18 42 38 28 38

2012Q4 deltaC 26 47 36 48 36

2012Q4 deltaD 28 36 46 78 46

run;

data WANT;

set HAVE;

array MONTH [5] MONTH03 MONTH06 MONTH09 MONTH12 MONTH15;

array NUM [5] NUM03 NUM06 NUM09 NUM12 NUM15;

array DEN [5] DEN03 DEN06 DEN09 DEN12 DEN15;

drop I;

do I=2 to 5;

NUM[I] = (MONTH[I]>0) * (MONTH[I]-MONTH[I-1]);

end;

do I=1 to 4;

if MONTH[I] ne . then

DEN[I] = lag3(MONTH[I]) - lag5(MONTH[I]) - lag6(MONTH[I]) + lag4(MONTH[I+1]) ;

end;

run;

 

 

 

Based on the assumption that each quarter always has 4 metrics.

Contributor
Posts: 29

Re: Calculate Percentage with rows and column calculation

Thanks Chris for reply. Numerator calculation works as expected.

 

For denominator

 

IF 2012Q4.DeltaD.Month03 <> .[null] then 2012Q3.DeltaC.Month03-(2012Q3.DeltaB.Month03+2012Q3.DeltaA.Month03)+2012Q3.DeltaC.Month06.

 

So denominator for month06 translates to below

2014Q4. Month06 = 28 <>[Null] then 23-(15+11)+45 else 0

 

YRQTR       METRIC  MONTH03   MONTH06   MONTH09   MONTH12   MONTH15;

2012Q3 deltaA    11           25        36        46        36

2012Q3 deltaB    15          40        36         24        36

2012Q3 deltaC    23          45        34         45        34

2012Q3 deltaD    23          33        44         76        44

2012Q4 deltaA    13          27        38         47        38

2012Q4 deltaB    18         42         38         28        38

2012Q4 deltaC    26         47         36         48        36

2012Q4 deltaD    28        36          46         78        46

 

Solution
‎06-20-2016 10:04 AM
PROC Star
Posts: 1,671

Re: Calculate Percentage with rows and column calculation

Like this?

 

 

do I=2 to 5;
 NUM[I] = (MONTH[I]>0) * (MONTH[I]-MONTH[I-1]);
end;
do I=1 to 4;
  DEN[I] = (MONTH[I]>0) * (lag5(MONTH[I]) - lag6(MONTH[I]) - lag7(MONTH[I]) + lag5(MONTH[I+1]) ) ;
end;
PROC Star
Posts: 1,671

Re: Calculate Percentage with rows and column calculation

Contributor
Posts: 29

Re: Calculate Percentage with rows and column calculation

Thanks Chris, it works great. Appreciate your help.

PROC Star
Posts: 1,671

Re: Calculate Percentage with rows and column calculation

Great! Now make sure you understand how it works so you've learned something. Smiley Happy

☑ This topic is solved.

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

Discussion stats
  • 8 replies
  • 452 views
  • 3 likes
  • 3 in conversation