Month over Month Change in Percent

[ScotchCat]

I use PC SAS and I've been challenged to create a Month over Month trending for the data sample example below. I'd like to show the change in the total number of accounts (account_ID) per month  - rolling forward month over month. (The data table I'll be using has 13 months of data.)   Thanks in advance!

Data_Have:

Account_ID     Month

123                 01/01/2020

456                 01/01/2020

055                 02/01/2020

077                 02/01/2020

951                 02/01/2020

159                 02/01/2020

357                 03/01/2020

038                 04/01/2020

999                 04/01/2020

Data_Need:

Month          Total_Accounts  Percent_Change

01/01/2020   2                        .

02/01/2020   4                        50

03/01/2020   1                        -75

04/01/2020   2                        50

[Kurt_Bremser · Accepted solution]

First, count per month:

proc sql;
create table counted as
  select
    month,
    count(*) as total_accounts
  from have
  group by month
;
quit;

then calculate the difference

data want;
set counted;
format percent_change percent5.;
percent_change = (total_accounts - lag(total_accounts) / lag(total_accounts);
run;

Note that, if a change from 4 to 1 is -75%, then a change from 2 to 4 is 100%.

[ScotchCat]

Thank You!! First experience with using Lag! Exactly what I was looking for - Perfect!!

[novinosrin]

data have;
input Account_ID     Month :mmddyy10.;
format month mmddyy10.;
cards;
123                 01/01/2020

456                 01/01/2020

055                 02/01/2020

077                 02/01/2020

951                 02/01/2020

159                 02/01/2020

357                 03/01/2020

038                 04/01/2020

999                 04/01/2020
;

data want;
 do until(last.month);
  set have;
  by month;
  Total_Accounts=sum(Total_Accounts,n(Account_ID));
 end;
 _n_=lag(Total_Accounts);
 if _n_ then  Percent_Change=divide(Total_Accounts-_n_,_n_);
 format Percent_Change percent10.2;
run;