Array- PCT change from month to next month

[Ronein]

Hello

For each customer I have one row with 12 columns of values per month (month 1 is last month, month 2 is one month before last month, month 3 is two months before last month and so on).

I want to calculate percentage change from one month to next month .

So Need to calculate:

PCT change from month 12 to month 11 (From value 90 to value  80  so it should be -0.11 calculated by (80-90)/90 )

PCT change from month 11 to month 10

PCT change from month 10 to month 9

PCT change from month 9 to month 8

PCT change from month 8  to month 7

PCT change from month 7 to month 6

PCT change from month 6 to month 5

PCT change from month 5 to month 4

PCT change from month 4 to month 3

PCT change from month 3 to month 2

PCT change from month 2 to month 1

However, My code doesn't calculate it well!!

for example:

PCT_change_12_To_11 should be  -0.11 but it shows 0.125

What is wrong in my code???

Data have;
input Rev12 Rev11 Rev10 Rev9 Rev8 Rev7 Rev6 Rev5 Rev4 Rev3 Rev2 Rev1;
cards;
90 80 85 89 94 90 78 75 85 -9 24 32
;
Run;

data want(drop=i);
set have;
array AAA{*}
Rev12
Rev11 
Rev10
Rev9 
Rev8 
Rev7
Rev6 
Rev5 
Rev4
Rev3
Rev2
Rev1; 
array PCT_change{*} 
PCT_change_12_To_11
PCT_change_11_To_10
PCT_change_10_To_9 
PCT_change_9_To_8 
PCT_change_8_To_7 
PCT_change_7_To_6 
PCT_change_6_To_5 
PCT_change_5_To_4 
PCT_change_4_To_3 
PCT_change_3_To_2
PCT_change_2_To_1;

do i=1 to dim(AAA)-1; 
if AAA[i+1] not in  (.,0) then PCT_change[i]=(AAA[i]-AAA[i+1])/AAA[i+1];
end;
run;

[Ksharp]

Should be :
if AAA[i+1] not in (.,0) then PCT_change[i]=(AAA[i+1]-AAA[i])/AAA[i];

[Ronein]

but in mathematics condition is that  nominator cannot be 0 .

In my code 1 is current month and 12 is the month one year ago.

Please look again in my code:

why calculation is wrong????

Data have;
input Rev12 Rev11 Rev10 Rev9 Rev8 Rev7 Rev6 Rev5 Rev4 Rev3 Rev2 Rev1;
cards;
0.96 0.96 0.97 0.84 0.83 0.87 0.87 0.77 0.80 0.97 0.94 0.94
;
Run;
data want(drop=i);
set have;
array AAA{*}
Rev1
Rev2 
Rev3
Rev4 
Rev5 
Rev6
Rev7 
Rev8 
Rev9
Rev10
Rev11
Rev12
 ; 
array PCT_change{*} 
PCT_change_1_2
PCT_change_2_3
PCT_change_3_4
PCT_change_4_5
PCT_change_5_6
PCT_change_6_7
PCT_change_7_8
PCT_change_8_9
PCT_change_9_10
PCT_change_10_11
PCT_change_11_12
;
do i=1 to dim(AAA)-1; 
if AAA[i+1] not in  (.,0) then PCT_change[i]=(AAA[i]-AAA[i+1])/AAA[i+1];
end;
run;

[Ksharp]

Then should be:
if AAA[i] not in (.,0) then PCT_change[i]=(AAA[i+1]-AAA[i])/AAA[i];

You reverse role of AAA[i] and AAA[i+1],so you get wrong result.

[Ronein]

Can you please show the full code?

Please remind that month 1 is last month and month 12 is the month one year ago

(i is current and i+1 is previous)

[PaigeMiller]

---

@Ronein wrote:

Can you please show the full code?

---

@Ksharp gives you the line of code to fix that will produce -0.11. From his post above

if AAA[i] not in (.,0) then PCT_change[i]=(AAA[i+1]-AAA[i])/AAA[i];

You could also use

PCT_change[i]=divide(AAA[i+1]-AAA[i],AAA[i]);

Also, Maxim 19.

[Tom]

Not clear what you want to do. 

The first values are REV12=90 and REV11=80.  To get the percent change as - 1/9 you need to treat 90 as baseline and 80 as the new value.  So that means a change from month 12 to month 11.  That seems backwards.

What is it that you measured?  What is it that you want to calculate?

[s_lassen]

I think the problem comes from the fact that you define the arrays "backwards" in time, from 12 to 1 instead of from 1 to 12. 

Take the first assignment in the array (I=1):

PCT_change[i]=(AAA[i]-AAA[i+1])/AAA[i+1];

resolves to 

PCT_change[1]=(AAA[1]-AAA[2])/AAA[2];

which again resolves to

PCT_change_12_To_11=(rev12-rev11)/rev11;

But what you want is

PCT_change_12_To_11=(rev11-rev12)/rev12;

which should be written as 

PCT_change[i]=(AAA[i+1]-AAA[i])/AAA[i];

and you should also change the IF condition:

if AAA[i] not in  (.,0) then PCT_change[i]=(AAA[i+1]-AAA[i])/AAA[i];

[Kurt_Bremser]

Maxim 19 at work:

data have;
input Rev12 Rev11 Rev10 Rev9 Rev8 Rev7 Rev6 Rev5 Rev4 Rev3 Rev2 Rev1;
cards;
90 80 85 89 94 90 78 75 85 -9 24 32
;

/* First, turn that stupid thing into usable data */

proc transpose data=have out=long1 (rename=(col1=value));
var rev:;
run;

data long2;
set long1;
month = input(substr(_name_,4),2.);
drop _name_;
run;

proc sort data=long2;
by month;
run;

/* Now the code is easy as it should be */
data want;
set long2;
PCT_change = ifn(_n_=1,.,(value - lag(value))/value);
format pct_change percent6.2;
run;

If you have multiple observations in the initial data, use some identifiying variable in BY statements and FIRST.[variable] as condition in the IFN.

[PaigeMiller]

---

@Kurt_Bremser wrote:

Maxim 19 at work:

/* Now the code is easy as it should be */
data want;
set long2;
PCT_change = ifn(_n_=1,.,(value - lag(value))/value);
format pct_change percent6.2;
run;

If you have multiple observations in the initial data, use some identifiying variable in BY statements and FIRST.[variable] as condition in the IFN.

---

In addition to the above, you can now attach a format to variable MONTH (if you want) so that when you create output, instead of months 1 through 12, you can have month names such as JAN or JAN2023, etc. This makes things easier for your audience, and looks more professional. Maxim 19 has many benefits!

[Astounding]

It seems you often look for ways to shorten your code.  This type of code is a good candidate:

if B not in (0, .) then C = A / B;

It can be shortened to:

if B then C = A / B;

When SAS interprets logical expressions as true or false, 0 and missing values are false and all other values (including negative numbers) are true.