Category: Data management
Author resource: Joost Impink
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 ;
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.
Other Data management |
---|
SAS Winsorize macro |
Latest forum posts |
---|
Fama French 49 YES 49 !!! by CA Miller |
date format by Emily |
Discretionary Accruals by jwhi121 |
question about discretionary accrual models by kerrida |
Combining Global and North American data by CA Miller |
File for Eventus or SAS Event Study by samme |
quarterly accruals by lxt88 |
Financial Statement Comparability - De Franco, Kothari and Verdi (2011, JAR) Replication by rowing |
question about discretionary accrual models by Zenghui |
problem in perl by KZ |