topic Re: Calculate lag of 6 Quater percentage change for 300 variables in SAS Enterprise Guide

Calculate lag of 6 Quater percentage change for 300 variables

droydroy — Fri, 03 Nov 2023 10:36:13 GMT

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.

Re: Calculate lag of 6 Quater percentage change for 300 variables

PaigeMiller — Fri, 03 Nov 2023 10:43:40 GMT

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

Re: Calculate lag of 6 Quater percentage change for 300 variables

droydroy — Fri, 03 Nov 2023 10:54:07 GMT

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

Re: Calculate lag of 6 Quater percentage change for 300 variables

PaigeMiller — Fri, 03 Nov 2023 11:00:06 GMT

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.

Re: Calculate lag of 6 Quater percentage change for 300 variables

droydroy — Fri, 03 Nov 2023 11:20:10 GMT

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.

Re: Calculate lag of 6 Quater percentage change for 300 variables

PaigeMiller — Fri, 03 Nov 2023 11:28:39 GMT

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;

Re: Calculate lag of 6 Quater percentage change for 300 variables

droydroy — Fri, 03 Nov 2023 11:32:00 GMT

Thanks a lot

Re: Calculate lag of 6 Quater percentage change for 300 variables

droydroy — Fri, 03 Nov 2023 11:39:48 GMT

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

Re: Calculate lag of 6 Quater percentage change for 300 variables

PaigeMiller — Fri, 03 Nov 2023 11:42:53 GMT

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;

Re: Calculate lag of 6 Quater percentage change for 300 variables

droydroy — Fri, 03 Nov 2023 11:52:50 GMT

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