Calculate lag of 6 Quater percentage change for 300 variables

[droydroy]

We have 300 variables in the data set. Each variable is having quaterly data , for an example starting from Q12010 to Q12020. Now I am trying to calculate for each variable what will be the lag value of 6 quater percentage change. So I need to create 6 quater percentage change of eaxh variable and then calculate their lag values. Please help.

[PaigeMiller]

Its not exactly clear to me how your data is arranged. Is it something like this

var1                         var2

data for q12010       data for q12010
data for q22010       data for q22010
data for q32010       data for q32010
...                              ...

If yes, then we can help you create a program. If no, then we need to know the arrangement of the data before we can be of any help, in this case SHOW US a portion of the data (we don't need all 300 variables, but 2 or 3 would be great).

[droydroy]

Period var1 var2 var3 ....... var276
2010Q1 2.3 9 10 ....... 50
2010Q2 5.3 24 44 ....... 3
2010Q3 18.4 16 15 ........ 55
2010Q4 20 2 3 ........ 4
2011Q1 2 5 18 ........ 11
2012Q2 5.6 2 22 ......... 50
2013Q3 7 10 30 ......... 5
2014Q4 1 30 50 ........ 18.5
Upto
2050Q4

[PaigeMiller]

Thanks, now please walk us through one example of performing the calculation of "lag of 6 Quater percentage change", using the above data that you provided.

[droydroy]

6qtr percentage change = [( value in 2011Q2 - value in 2010Q1)/ value in 2010Q1]*100

Need to code in such a way that it can be used for 3qtr / 10 Qtr / 12 Qtr etc.

[PaigeMiller · Accepted solution]

First I provide code where the 6 month is hard-coded, and then I modify the code to allow for 10 month or 12 month or whatever.

/* UNTESTED CODE */
data want;
    set have;
    array v var1-var276;
    array pctchg pctchg1-pctchg276;
    do i=1 to dim(v);
         pctchg(i)=100*(v(i)-lag5(v(i)))/lag5(v(i)); /* 6 months ago is found via lag5 */
     end;
run;

Example code where you can change the lag

/* UNTESTED CODE */
%let lag_desired=10;
%let lag_to_use=%eval(&lag_desired-1);

data want;
    set have;
    array v var1-var276;
    array pctchg pctchg1-pctchg276;
    do i=1 to dim(v);
         pctchg(i)=100*(v(i)-lag&lag_to_use(v(i)))/lag&lag_to_use(v(i)); 
     end;
run;

[droydroy]

Thanks a lot

[droydroy]

Shall I create another loop to calculate lag of of these percentage change values ?
Can that step be incorporated in this loop ?

Aim is to calculate lag1 or lagi for these percentage change values

[PaigeMiller]

If I am understanding you properly

data want;
    set have;
    array v var1-var276;
    array pctchg pctchg1-pctchg276;
    array lagpctchg lagpctchg1-lagpctchg276;
    do i=1 to dim(v);
         pctchg(i)=100*(v(i)-lag5(v(i)))/lag5(v(i)); /* 6 months ago is found via lag5 */
         lagpctchg(i)=lag(pctchg(i));
     end;
     drop i;
run;

[droydroy]

Now I understood the logic behind arrays. Thanks a lot for teaching.