BookmarkSubscribeRSS Feed
wrshif
Fluorite | Level 6

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 

5 REPLIES 5
Shmuel
Garnet | Level 18

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.

wrshif
Fluorite | Level 6

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     .

 

 

Shmuel
Garnet | Level 18

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;

 

 

          

wrshif
Fluorite | Level 6

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

mkeintz
PROC Star

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;

  
--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 899 views
  • 3 likes
  • 3 in conversation