Calculate growth rates for several variables at the same time

Question

I would like to calculate the growth rate of several variables without doing it manually. Is there any smart way to do it?

E.g. See table below from sashelp.citiyr that looks like:

DATE    PAN     PAN17   PAN18   PANF    PANM
1980    227757  172456  138358  116869  110888
1981    230138  175017  140618  118074  112064
1982    232520  177346  142740  119275  113245
1983    234799  179480  144591  120414  114385
1984    237001  181514  146257  121507  115494
1985    239279  183583  147759  122631  116648
1986    241625  185766  149149  123795  117830
1987    243942  187988  150542  124945  118997
1988    246307  189867  152113  126118  120189
1989    248762  191570  153695  127317  121445

I can create the growth rate of the variables as follows (example for first column PAN) but I would like a way to compute it for all the variables (or those I want, imagine a case with dozens of them).

data test; 
    set sashelp.citiyr; 
    by date;
    Pan_growth = PAN / lag(PAN);
run;

Any idea how to make this smarter?

score 2 · Answer 1 · answered Jan 22 '20 at 17:59

Transpose your data set and use BY group processing.

   *transpose to a long format;
   proc transpose data=have out=long (rename=col1=Value);
   by date;
   var pan--panm;
   run;

   *sort for each variable to be consistent;
   proc sort data=long;
   by _name_ date;
   run;

   *calculate lag;
   data want;
   set long;
   by _name_;
   prev_val = lag(value);


   if not(first._name_) then growth = value/prev_value - 1;
   run;

score 2 · Answer 2 · answered Jan 23 '20 at 07:56

You could use macros. Also to avoid get warnings you need some conditional logic to prevent division in first row

data test; 
    set sashelp.citiyr; 
    by date;
    %macro growth(var);
        before=lag(&var);
        if _N_>1 then &var._growth = &var. / before;
        drop before;
    %mend;
    %growth(PAN);
    %growth(PAN17);
    %growth(PAN18);
    %growth(PANF);
    %growth(PANM);
run;

score 1 · Accepted Answer · answered Jan 22 '20 at 16:04

Use arrays.

data test;
    set sashelp.citiyr;

    array vars[*]   pan pan17 pan18 panf panm;
    array growth[*] pan_growth pan17_growth pan18_growth panf_growth panm_growth;

    do i = 1 to dim(vars);
        growth[i] = vars[i]/lag(vars[i]);
    end;
run;

If your variables all start with a certain prefix, end in sequential numbers, or are always in the exact same order, you can save even more time by using variable list shortcuts.

If you have an even more complex case where you have hundreds of variables that aren't in the right order or have no simple pattern, you can generate the desired names and save them into macro lists using SQL and dictionary.columns. Just make sure you exclude any irrelevant variables from your query.

proc sql noprint;
    select name
         , cats(name, '_growth')
    into :vars separated by ' '
       , :growth_vars separated by ' '
    from dictionary.columns
    where     libname = 'SASHELP'
          AND memname = 'CITIYR'
          AND upcase(name) NE 'DATE'
    ;
quit;

data test2;
    set sashelp.citiyr;

    array vars[*]   &vars.;
    array growth[*] &growth_vars.;

    do i = 1 to dim(vars);
        growth[i] = vars[i]/lag(vars[i]);
    end;
run;

Calculate growth rates for several variables at the same time

3 Answers3