Macro to adjust timeseries data for inflation (CPI)

Category: Data management

Author resource: Joost Impink

Explanation

This dataset with price indices and macro can be used to adjust time-series data for inflation. It can be used (for example) to include firm-years where sales exceed $50 million (in 2010 dollars).

A 'base' year as well as the variables to adjust need to be specified. The variables in other than the base-year will be scaled upwards/downwards accordingly to the price level relative to this base year. The inflation-adjusted variables will be appended to the provided input dataset. That is, if sale (sales) and at (assets) are the variables that need adjusting, then the adjusted dataset will include sale_adj and at_adj as additional (adjusted) variables.

The price index used is Consumer Price Index, All Urban Consumers - (CPI-U), U.S. city average from the U.S. Department Of Labor.

Important: this macro uses the %array and %do_over macros from Clay (available here).

/*
	Macro: cpiAdjust

	Purpose: Adjust time-series data for inflation

	Variables
	=========
		dsin		dataset in, needs year variable
		dsout		dataset out, will be dsin with variable(s) appended
		variables	list of variables separated by a space 
					(for example: variables=sale at ceq)
	
	
	Price index data
	================
		U.S. Department Of Labor, Bureau of Labor Statistics, Washington, D.C. 20212
		Consumer Price Index, All Urban Consumers - (CPI-U), U.S. city average
		ftp://ftp.bls.gov/pub/special.requests/cpi/cpiai.txt

	

*/


/*	
	Dataset with price index (CPI-U)
	('datalines' cannot go in a macro) */

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
;

/*	Macro computedAdjusted is a macro that is called by 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;



%macro cpiAdjust(dsin=, dsout=, baseyear=, variables=);

	%array(vars, 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;
	quit;

	/*	Create output dataset, with variables adjusted */

	data &dsout;
	set work.cpi_temp;
		%DO_OVER(vars, MACRO = computedAdjusted);
	run;

	/*	Clean up */

	proc datasets library=work;
	   delete cpi_temp;
	run;

%mend ;

Usage

Sample usage:

/*	Sample set Funda */

data work.sample (keep = gvkey fyear at sale);
set comp.funda;
if indfmt='INDL' and datafmt='STD' and popsrc='D' and consol='C' ;
run;

/*	Invoke macro */

%cpiAdjust(dsin=work.sample, dsout=work.sample_adj, baseyear=2005, variables=sale at);

Dataset ‘sample_adj’ will contain sale and at, as well as the inflation-adjusted variables sale_adj and at_adj.