topic Month over Month Change in Percent in New SAS User

Month over Month Change in Percent

ScotchCat — Fri, 25 Sep 2020 14:28:24 GMT

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

Re: Month over Month Change in Percent

Kurt_Bremser — Fri, 25 Sep 2020 14:47:12 GMT

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%.

Re: Month over Month Change in Percent

ScotchCat — Fri, 25 Sep 2020 15:05:28 GMT

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

Re: Month over Month Change in Percent

novinosrin — Fri, 25 Sep 2020 15:53:10 GMT


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;