Using %ARRAY and %DO_OVER Macros to adjust time series data for Inflation

[jjsingh04]

I've been trying to adapt the SAS code found at http://www.wrds.us/index.php/repository/view/28

to adjust my data for inflation for the years 1997 through 2018. But I keep getting the error messages "ERROR 180-322: Statement is not valid or it is used out of proper order." I think these errors are happening at both the %ARRAY and the %DO_OVER lines, which make sense as places for errors to happen, because I can't figure out what to replace the "VARS" with there in the original code. In the original code, the lines are:  %array(vars, VALUES=&variables);and %DO_OVER(vars, MACRO = computedAdjusted);, which don't make sense to me. I've tried various adaptations, but nothing works. I've scoured the internet looking for answers but have found nothing yet. Any help would be greatly appreciated. My most recent code follows. 

data work.cpi;
input year levelCPI;
datalines;
1913    9.9
1914    10
1915    10.1
1916    10.9
1917    12.8
1918    15.1
1919    17.3
1920    20
1921    17.9
1922    16.8
1923    17.1
1924    17.1
1925    17.5
1926    17.7
1927    17.4
1928    17.1
1929    17.1
1930    16.7
1931    15.2
1932    13.7
1933    13
1934    13.4
1935    13.7
1936    13.9
1937    14.4
1938    14.1
1939    13.9
1940    14
1941    14.7
1942    16.3
1943    17.3
1944    17.6
1945    18
1946    19.5
1947    22.3
1948    24.1
1949    23.8
1950    24.1
1951    26
1952    26.5
1953    26.7
1954    26.9
1955    26.8
1956    27.2
1957    28.1
1958    28.9
1959    29.1
1960    29.6
1961    29.9
1962    30.2
1963    30.6
1964    31
1965    31.5
1966    32.4
1967    33.4
1968    34.8
1969    36.7
1970    38.8
1971    40.5
1972    41.8
1973    44.4
1974    49.3
1975    53.8
1976    56.9
1977    60.6
1978    65.2
1979    72.6
1980    82.4
1981    90.9
1982    96.5
1983    99.6
1984    103.9
1985    107.6
1986    109.6
1987    113.6
1988    118.3
1989    124
1990    130.7
1991    136.2
1992    140.3
1993    144.5
1994    148.2
1995    152.4
1996    156.9
1997    160.5
1998    163
1999    166.6
2000    172.2
2001    177.1
2002    179.9
2003    184
2004    188.9
2005    195.3
2006    201.6
2007    207.342
2008    215.303
2009    214.537
2010    218.056
2011    224.939
2012    229.594
2013    232.957
2014    236.736
2015    237.017
2016    240.007
2017    245.120
2018    251.107
2019    255.657
;


/*  Sample set cSlix */
 
data work.cSlix (keep = RANDOM gvkey datadate fyear indfmt tic cusip conm curcd at bkvlps capx ceq csho cstk dlc dltt dp dt ebit ebitda emp ib lt ni oancf oibdp ppent pstkrv sales seq wcapc wcapch xrd costat sic mkvalt prcc_f rank au fyrc incorp re wcap);
set work.cSlix;
if indfmt ne '.';
run;

/*  The macro computedAdjusted is a macro that is called by the main macro cpiAdjust
    and is called for each of the variables passed to cpiAdjust in 'variables' 
 
    This macro generates new variable as old variable divided by relative CPI index
    For example: sale_adj = sale/relativeBase 						*/
 
%macro computedAdjusted(var);
    &var._adj = &var/relativeBase;
%mend;


 /* For more info on the 2 SAS macros called %ARRAY and %DO_OVER (which we use below), see
https://support.sas.com/resources/papers/proceedings/proceedings/sugi31/040-31.pdf     
and https://www.lexjansen.com/pnwsug/2006/PN22TedClayFiveMacros.pdf                    */
 
%macro cpiAdjust(dsin=work.cSlix, dsout=work.colix, baseyear=1997, variables=at bkvlps capx ceq csho cstk dlc dltt dp dt ebit ebitda emp ib lt ni oancf oibdp ppent pstkrv sales seq wcapc wcapch xrd mkvalt prcc_f re wcap);
 
    %ARRAY(var, at bkvlps capx ceq csho cstk dlc dltt dp dt ebit ebitda emp ib lt ni oancf oibdp ppent pstkrv sales seq wcapc wcapch xrd mkvalt prcc_f re wcap, VALUES=&variables);
 
    /*  Get price index for base year */
 
    data _null_;
    set work.cpi;
    if year eq &baseyear then CALL SYMPUT("baseCPI",levelCPI); 
    run;
 
    /*  Append relativeBase to dataset   (divide year's price level by base year '&baseCPI') */
 
    proc sql;
 
        create table work.cpi_temp as
        select a.*, b.levelCPI/&baseCPI as relativeBase
        from
            &dsin a
        LEFT JOIN
            work.cpi b
        on
            a.fyear = b.year; /* Note: In the case of this PARTICULAR DSIN (cSlix) only, it's called fyear */
    quit;
 
    /*  Create the output dataset, with variables adjusted */
 
    data &dsout;
    set work.cpi_temp;
        %DO_OVER(var, at bkvlps capx ceq csho cstk dlc dltt dp dt ebit ebitda emp ib lt ni oancf oibdp ppent pstkrv sales seq wcapc wcapch xrd mkvalt prcc_f re wcap, %computedAdjusted); 
    run;
 /* Just above, I changed "%DO_OVER(vars, MACRO = computedAdjusted);" to "%DO_OVER(var, ... %computedAdjusted); */

    /*  Clean up */
 
    proc datasets library=work;
       delete cpi_temp;
    run;
 
%mend ;

/*  Invoke macro */
 
%cpiAdjust(dsin=work.cSlix, dsout=work.colix, baseyear=1997, variables=at bkvlps capx ceq csho cstk dlc dltt dp dt ebit ebitda emp ib lt ni oancf oibdp ppent pstkrv sales seq wcapc wcapch xrd mkvalt prcc_f re wcap);

[ballardw]

When debugging a macro one of the first things to do is set

OPTIONS MPRINT;

so the log will show the generated code (along with some other stuff) and then the error messages usually appear near the generated code that causes the error. If you are not able to figure out from that log then copy the log for at least the data step or procedure that generated the error.

Did you compile the macro before attempting to use it? That would require submitting the code that starts with

%macro %array (<parameters>);

<the actual macro code>

%mend;

%macro %do_Over(<parameters>);

...

%mend;

Where the stuff I put in < > above would be  part of the code of the macro . The macro has to be made available to your current session before use by submitting that code.

That may involve downloading the source from that link and including it in your code.

[jjsingh04]

Thank you very much for your advice! I appreciate it!

J.J. 

[yabwon · Accepted solution]

Hi,

if I may offer you my version of the %array and %do_over macros from MacroArray package.

Get the package and enable it:

/* get the MacroArray Package from the github */
options dlcreatedir;
libname _ "%sysfunc(pathname(work))/packages";

filename in url "https://raw.githubusercontent.com/yabwon/SAS_PACKAGES/master/loadpackage.sas" recfm=n lrecl=1;
filename out "%sysfunc(pathname(_))/loadpackage.sas" recfm=n lrecl=1;
data _null_;
   rc=fcopy('in', 'out');
   put rc=;
run;

filename in url "https://raw.githubusercontent.com/yabwon/SAS_PACKAGES/master/macroarray.zip" recfm=n lrecl=1;
filename out "%sysfunc(pathname(_))/macroarray.zip" recfm=n lrecl=1;
data _null_;
   rc=fcopy('in', 'out');
   put rc=;
run;

filename packages "%sysfunc(pathname(_))";
%include packages(loadpackage.sas);
%helpPackage(macroarray); /* <- read the help info */
%loadPackage(macroarray);

if you can't download it automatically (with code above) just make yourself a folder, e.g. "C:\SAS_PACKAGES", go to the github: https://github.com/yabwon/SAS_PACKAGES and download: loadpackage.sas and macroarray.zip into the "C:\SAS_PACKAGES" and then run:

filename packages "C:\SAS_PACKAGES";
%include packages(loadpackage.sas);
%helpPackage(macroarray); /* <- read the help info */
%loadPackage(macroarray);

and than run your macro adjusted in the following way:

%macro cpiAdjust(dsin=work.cSlix, dsout=work.colix, baseyear=1997, variables=at bkvlps capx ceq csho cstk dlc dltt dp dt ebit ebitda emp ib lt ni oancf oibdp ppent pstkrv sales seq wcapc wcapch xrd mkvalt prcc_f re wcap);
 
    /* create array */
    %array(varmames[*] &variables, macarray=Y, vnames=Y); /* run and read LOG: %helpPackage(macroarray,%nrstr(%array())) */

 
    /*  Get price index for base year */
 
    data _null_;
    set work.cpi;
    if year eq &baseyear then CALL SYMPUT("baseCPI",levelCPI); 
    run;
 
    /*  Append relativeBase to dataset   (divide year's price level by base year '&baseCPI') */
 
    proc sql;
 
        create table work.cpi_temp as
        select a.*, b.levelCPI/&baseCPI as relativeBase
        from
            &dsin a
        LEFT JOIN
            work.cpi b
        on
            a.fyear = b.year; /* Note: In the case of this PARTICULAR DSIN (cSlix) only, it's called fyear */
    quit;
 
    /*  Create the output dataset, with variables adjusted */
 
    data &dsout;
    set work.cpi_temp;
        /* loop over macro array with Phrase you need*/
        %do_over(varmames
           , phrase = %nrstr(%computedAdjusted(%varmames(&_I_.))) /* &_I_ is the default iterator for do_over */
           )
    run;
 
    /*  Clean up */
 
    proc datasets library=work;
       delete cpi_temp;
    run;
 
    /* delete macroarray */
    %deleteMacArray(varmames, macarray = Y)


%mend ;

/*  Invoke macro */
 
%cpiAdjust(dsin=work.cSlix, dsout=work.colix, baseyear=1997, variables=at bkvlps capx ceq csho cstk dlc dltt dp dt ebit ebitda emp ib lt ni oancf oibdp ppent pstkrv sales seq wcapc wcapch xrd mkvalt prcc_f re wcap);

Hope it helps 🙂

Bart

[jjsingh04]

Your code works brilliantly Bartosz! Thank you so very much! 

Also, I watched your presentation at SGF on SAS Packages--what a clever idea.

J.J.

[jjsingh04]

PS: In case anyone is reading this webpage and wondering about them, I've realized that the variables CSHO and EMP should not have been included for inflation adjustment and have since removed them from my analysis.

J.J.

[yabwon]

Thanks! I'm glad you liked it. 🙂 Spread the word. 🙂
Bart