Hi All,
I have a dataset in the following format:
year bank_ID cash_balance
2000 1 0
2000 2 0
2000 3 0
2000 4 0
2001 1 10
2001 2 20
2001 3 0
2001 4 5
2002 1 15
2002 2 22
2002 3 5
2002 4 7
.
.
.
2009.......
Now, I would like to create three variables, time_1, time_2, time_3. These three time variables represent presence or absense (0/1) of a bank balance of >$0 for a particular bankID in the previous year, in two years previous, and three year previous.
Example 1. When SAS is evaluating year 2000, then it should create variables temp_1, temp_2, and temp_3 based on bank balances for the unique bankIDs during years 1999, 1998, and 1997. Since data for years 1997-1999 are missing, temp_1, temp_2, and temp_3 values for year 2000 should have missing values. In case of year 2001, data for years 1998 and 1999 are missing, so temp_2 and temp_3 values for year 2001 should be set to missing.
Example 2. For year 2003 and bankID 1, bank balance for year 2002 (temp_1) is 15, so temp_1 would be 1, however for 2000 (temp_3) the bank_balance is 0, so value of temp_3 would be 0.
Any suggestions regarding an appropriate SAS program to process this data will be highly appreciated. Thank you.