Arrays Growth Rates

[Afor910327]

Hi there,

Does anyone know know to make a "nice" code using arrays with the following concept?

Thanks in advance.

data  Rgo6;
set Rgo;
Growth_1999=((_999/_998)-1);
Growth_2000=((_000/_999)-1);
Growth_2001=((_001/_000)-1);
Growth_2002 =((_002/_001)-1);
Growth_2003=((_003/_002)-1);
Growth_2004=((_004/_003)-1);
Growth_2005=((_005/_004)-1);
Growth_2006=((_006/_005)-1);
Growth_2007=((_007/_006)-1);
Growth_2008=((_008/_007)-1);
Growth_2009=((_009/_008)-1);
Growth_2010=((_010/_009)-1);
Growth_2011=((_011/_010)-1);
Growth_2012=((_012/_011)-1);
Growth_2013=((_013/_012)-1);
Growth_2014=((_014/_013)-1);
Growth_2015=((_015/_014)-1);
Growth_2016=((_016/_015)-1);
Growth_2017=((_017/_016)-1);
format  Growth_1999  percent10.1;
format  Growth_2000  percent10.1;
format  Growth_2001  percent10.1;
format  Growth_2002  percent10.1;
format  Growth_2003  percent10.1;
format  Growth_2004  percent10.1;
format  Growth_2005  percent10.1;
format  Growth_2006  percent10.1;
format  Growth_2007  percent10.1;
format  Growth_2008  percent10.1;
format  Growth_2009  percent10.1;
format  Growth_2010  percent10.1;
format  Growth_2011  percent10.1;
format  Growth_2012  percent10.1;
format  Growth_2013  percent10.1;
format  Growth_2014  percent10.1;
format  Growth_2015  percent10.1;
format  Growth_2016  percent10.1;
format  Growth_2017  percent10.1;
keep itemACPSA;
keep itemACPSADescr;
keep Growth_1999;
keep Growth_2000;
keep Growth_2001;
keep Growth_2002 ;
keep Growth_2003;
keep Growth_2004;
keep Growth_2005;
keep Growth_2006;
keep Growth_2007;
keep Growth_2008;
keep Growth_2009;
keep Growth_2010;
keep Growth_2011;
keep Growth_2012;
keep Growth_2013;
keep Growth_2014;
keep Growth_2015;
keep Growth_2016;
keep Growth_2017;
RUN;

[Patrick · Accepted solution]

You could do something as below.

%macro typeForMe();
  %do i=1999 %to 2017;
    format  Growth_&i  percent10.1;
    Growth_&i=((_%substr(&i,2)/_%substr(%eval(&i-1),2))-1);
    keep Growth_&i;
  %end;
%mend;

options mprint;
data  rgo6;
  set rgo;
  keep itemacpsa;
  keep itemacpsadescr;
  %typeForMe()
run;

Or here another (not tested) way of doing this.

data  Rgo6;
  set Rgo;
  keep itemACPSA;
  keep itemACPSADescr;

  array Growth {*} 8 Growth_1999-Growth_2017;
  format Growth_: percent10.1;
  keep Growth_:;

  array source {*} _998 - _016;

  do _i=2 to dim(source);
    Growth[_i-1]=((source[_i-1]/source[_i])-1);
  end;

  drop _i;
run;

And here another option how to generate code. I personally prefer this approach over using a SAS Macro because during development you can use "file print" and easily verify that the generated code is what you want.

filename codegen temp;
data _null_;
/*  file print;*/
  file codegen;
  do i=1999 to 2017;
    put 'format Growth_' i ';';
    put 'keep Growth_' i ';';
    x1=substrn(put(i,z4.),2);
    x2=substrn(put(i-1,z4.),2);
    put 'Growth_' i '= ((_' x1 '/_' x2 ')-1);'; 
  end;
  stop;
run;

data  Rgo6;
  set Rgo;
  keep itemACPSA;
  keep itemACPSADescr;
  %include codegen / source2;
run;

filename codegen clear;

[Afor910327]

Very nice, thank you Patrick!

[Kurt_Bremser]

What you have is a consequence of bad design, or being stuck in "Excel thinking".

Transpose your dataset to a long format, and the code will be as simple as can be by using the lag() function to compare with the previous observation.