topic Matching CPI Values to Years in SAS Programming

Matching CPI Values to Years

wrshif — Mon, 23 Jan 2017 16:54:42 GMT

Hi All,

I have a data set (A) that included the year someone takes out a loan and a data set (B) that includes Consumer Price Index values by year.

I'd like to check the year in data set A and match it with a corresponding CPI value from data set B so that I can account for inflation.

I'm not sure if this can be done with a merge or if a merge is the most efficient way to proceed. Can anyone advise?

Thanks,

William

Re: Matching CPI Values to Years

Shmuel — Mon, 23 Jan 2017 17:01:40 GMT

You haven't post any test data (tables A & B) nor example of wanted result.

You can use sql join to mergae the two tables on a.year = b.year.

Re: Matching CPI Values to Years

wrshif — Mon, 23 Jan 2017 17:18:17 GMT

Thanks- I should clarify.

There are as many as six loans recorded for each observation in data set A, so there are potentially six unique dates for when the loan was taken out.

I can do six merges, but I'm almost sure there is a better way to handle this. Here's what I'd like to do:

Data set A:

X7804 X7827 X7850 X7904 X7927 X7950

1913 1913 1914 1915 1916 .

...

Data set B:

Year CPI

1913 9.783333333
1914 9.9
1915 10.03333333
1916 10.56666667
1917 12.35
...

Desired Data Set:

X7804 X7827 X7850 X7904 X7927 X7950 CPI1 CPI2 CPI3 CPI4 CPI5 CPI6

1913 1913 1914 1915 1916 . 9.783 9.783 9.9 10.03 10.57 .

Re: Matching CPI Values to Years

Shmuel — Mon, 23 Jan 2017 17:30:54 GMT

1) Create a format to get the CPI of a given year:

data cntl;

set B;

start = year;

label = put(cpi, z12.7);

fmtname= 'CPI_Y';

run;

proc format lib=work cntlin=cntl; run;

2) data want;

set A;

array yx X7804 X7827 X7850 X7904 X7927 X7950;

array cx CPI1-CPI6;

do i=1 to 6;

cx(i) = input(put(yx(i), cpi_y.),best12.7);

end;

run;

Re: Matching CPI Values to Years

mkeintz — Mon, 23 Jan 2017 19:13:19 GMT

Make an array of cpi lookup values indexed by year. Then use it for each of your 6 loans per record:

data want (drop=Y);
  array cpi_lookup {1910:2016} _temporary_;
  /* First Populate the lookup array */
  if _n_=1 then do until (end_of_cpi);
    set b end=end_of_cpi;
    cpi_lookup{year}=cpi;
  end;

  set a;

  array cpi{6}       CPI7804 CPI7827 CPI7850 CPI7904 CPI7927 CPI7950 ;
  array loanyear {6} X7804 X7827 X7850 X7904 X7927 X7950 ;
  do Y=1 to dim(loanyear);
    if loanyear{Y}^=. then cpi{Y}=cpi_lookup{loanyear{Y}};
  end;

run;

Re: Matching CPI Values to Years

wrshif — Mon, 23 Jan 2017 20:06:49 GMT

Thank you so much, Shmuel! This worked perfectly and led me to learn more about the cntlin= option here http://www2.sas.com/proceedings/forum2007/068-2007.pdf