topic Re: Calculate Percentage with rows and column calculation in SAS Programming

Calculate Percentage with rows and column calculation

sasuser101 — Wed, 08 Jun 2016 04:24:11 GMT

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

Re: Calculate Percentage with rows and column calculation

Reeza — Wed, 08 Jun 2016 07:55:08 GMT

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

Re: Calculate Percentage with rows and column calculation

sasuser101 — Mon, 13 Jun 2016 00:38:38 GMT

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

Re: Calculate Percentage with rows and column calculation

ChrisNZ — Mon, 13 Jun 2016 04:33:41 GMT

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.

Re: Calculate Percentage with rows and column calculation

sasuser101 — Wed, 15 Jun 2016 14:54:07 GMT

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

Re: Calculate Percentage with rows and column calculation

ChrisNZ — Wed, 15 Jun 2016 23:04:05 GMT

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;

Re: Calculate Percentage with rows and column calculation

ChrisNZ — Sun, 19 Jun 2016 21:35:42 GMT

Did this work?

Re: Calculate Percentage with rows and column calculation

sasuser101 — Mon, 20 Jun 2016 14:05:10 GMT

Thanks Chris, it works great. Appreciate your help.

Re: Calculate Percentage with rows and column calculation

ChrisNZ — Mon, 20 Jun 2016 21:43:14 GMT

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