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);
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
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.
Thank you very much for your advice! I appreciate it!
J.J.
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
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.
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.
Thanks! I'm glad you liked it. 🙂 Spread the word. 🙂
Bart
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.