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
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;
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
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
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.
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
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;
Did this work?
Thanks Chris, it works great. Appreciate your help.
Great! Now make sure you understand how it works so you've learned something. 🙂
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.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.