topic Re: Array- PCT change from month to next month in SAS Programming

Array- PCT change from month to next month

Ronein — Mon, 11 Nov 2024 08:23:22 GMT

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;

Re: Array- PCT change from month to next month

Ksharp — Mon, 11 Nov 2024 08:39:11 GMT

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

Re: Array- PCT change from month to next month

Ronein — Mon, 11 Nov 2024 08:53:59 GMT

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;

Re: Array- PCT change from month to next month

Ksharp — Mon, 11 Nov 2024 09:09:35 GMT

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.

Re: Array- PCT change from month to next month

Ronein — Mon, 11 Nov 2024 09:37:10 GMT

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)

Re: Array- PCT change from month to next month

PaigeMiller — Mon, 11 Nov 2024 12:26:53 GMT

@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.

Re: Array- PCT change from month to next month

Tom — Mon, 11 Nov 2024 18:24:17 GMT

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?

Re: Array- PCT change from month to next month

s_lassen — Tue, 12 Nov 2024 07:59:00 GMT

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];

Re: Array- PCT change from month to next month

Kurt_Bremser — Tue, 12 Nov 2024 12:57:36 GMT

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.

Re: Array- PCT change from month to next month

PaigeMiller — Tue, 12 Nov 2024 14:00:17 GMT

@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!

Re: Array- PCT change from month to next month

Astounding — Tue, 12 Nov 2024 14:53:03 GMT

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.