How to create a new column that returns values from previous month

[Tony_N]

Hi, 

I'm working with a dataset that looks something like below:

account	month_end	active_stat
1	31-Jan-20	yes
1	29-Feb-20	yes
1	31-Mar-20	yes
2	31-Jan-20	yes
2	29-Feb-20	no
2	31-Mar-20	no
3	31-Jan-20	no
3	29-Feb-20	no
3	31-Mar-20	yes

Account field: unique account number

Month_end: date

active_stat: indicator showing whether an account is active or not during that month

I'd like to get help with creating a new column that returns active status of the accounts in the data sets in previous month. Below is example of the output i'd like to achieve, where prevmon_stat shows active status of the account in previous month.

account	month_end	active_stat	prevmon_stat
1	31-Jan-20	yes
1	29-Feb-20	yes	yes
1	31-Mar-20	yes	yes
2	31-Jan-20	yes
2	29-Feb-20	no	yes
2	31-Mar-20	no	no
3	31-Jan-20	no
3	29-Feb-20	no	no
3	31-Mar-20	yes	no

Thank you for your help!

[Tom · Accepted solution]

You could use the LAG() function, which returns the value from the previous time it executes.  

data want;
  set have;
  by account;
  prevmon_stat=lag(active_stat);
  if first.account then call missing(prevmon_stat);
run;

Note that it knows nothing about months. So if your data is missing a month or has a two observations for the same month then the prevmon_stat value is not from the previous month. It is just the value that it remembered from the previous time it executed.

[Tony_N]

Hi Tom,

It works! Thank you so much for your quick solution