topic Re: Calculate difference between current month and previou month balance in SAS Programming

Calculate difference between current month and previou month balance

Sandy10 — Mon, 20 Apr 2020 09:39:20 GMT

Hi,

I have a SA dataset as below. Customer ID and their MoM balance(Apr17- Monthyear). I wnat to calculate growth in each month. i.e, May growth is SAV_May17-SAV-Apr17

Customer ID	SAV_APR17	SAV_May17	SAV_Jun17
A	100	200	30
B	200	300	500
CD	100	600	1000

Output:

Customer ID	May growth	Jun growth
A	100	-170
B	100	200
CD	500	400

How to do this in SAS?

Please note that I can change column names.

Thanks and Regards

Sandhya S

Re: Calculate difference between current month and previou month balance

Krabkvadrat — Mon, 20 Apr 2020 09:47:40 GMT

Hello, try this

proc sql;
create table output_data_set as
select
id
,(may-apr) as may_growth
,(jun-may) as jun_growth
from input_data_set
;quit

Re: Calculate difference between current month and previou month balance

Sandy10 — Mon, 20 Apr 2020 09:56:47 GMT

Hi. Yes I cantry this. Just that its a huge dataset of half a million records and 27 date columns.

Hence wa trying to know any function or soething

Thanks

Re: Calculate difference between current month and previou month balance

Jagadishkatam — Mon, 20 Apr 2020 10:08:45 GMT

Please try the below code

data have;
input Customer_ID$ SAV_APR17 SAV_May17 SAV_Jun17;
cards;
A 100 200 30
B 200 300 500
CD 100 600 1000
;

data want;
set have;
array vals(3) SAV_APR17 SAV_May17 SAV_Jun17;
array vals2(3) may june july;
do i = 1 to 3;
if i<=3 then vals2(i)=vals(i)-vals(i+(i<3));
end;
run;

Re: Calculate difference between current month and previou month balance

Kurt_Bremser — Mon, 20 Apr 2020 10:32:41 GMT

You suffer from an unfavorable data structure; with a longitudinal structure, the code is a breeze:

So let's first bring your wide dataset (probably coming from an Excel sheet) into form, and make SAS dates out of your columns names:

data have_wide;
input Customer_ID$ SAV_APR17 SAV_May17 SAV_Jun17;
cards;
A 100 200 30
B 200 300 500
CD 100 600 1000
;

proc transpose data=have_wide out=trans (rename=(col1=sav));
by customer_id;
var sav:;
run;

data have;
set trans;
period = input(substr(_name_,5),monyy5.);
format period yymms7.;
drop _name_;
run;

We then make sure that the dataset is sorted, and create the wanted result:

proc sort data=have;
by customer_id period;
run;

data want;
set have;
by customer_id;
growth = sav - lag(sav);
if first.customer_id then growth = .;
run;

You can see how simple the data step that does all the work becomes, as soon as you have an intelligent data structure. See Maxims 19 & 33.

Re: Calculate difference between current month and previou month balance

Sandy10 — Mon, 20 Apr 2020 10:43:59 GMT

@Kurt_Bremser Thanks a lot for your reply. I have a dataset which is about half a million customers
and 27 date columns. WIll this code be still quick and be used?

Re: Calculate difference between current month and previou month balance

Kurt_Bremser — Mon, 20 Apr 2020 11:32:09 GMT

So you get 500000 * 27 observations, each having an observation size of

length(id) + 8 (date) + 8(sav) + 8 (growth), or

500000 * 27 * 34 (if id is 10 bytes long).

This amounts to 459000000 bytes, or approx. 450 MB. That's puny in all respects (think: about 100 photos on the SD card of your high-res camera).

You can even run such datasets through the rather limited University Edition.

Re: Calculate difference between current month and previou month balance

Kurt_Bremser — Mon, 20 Apr 2020 11:39:28 GMT

And think about this: in your wide dataset, each month for each customer will need space, even if the customer has no value for that month. In a long dataset, you can omit missing or zero observations, as in most calculations you will do you can use the date values to calculate the timespan between observations. That offsets the space needed for the additional date (period) column.

Re: Calculate difference between current month and previou month balance

ballardw — Mon, 20 Apr 2020 14:23:53 GMT

Additionally the long form with an actual date value will allow things like graphs with date as the xaxis that the original data would not support.

Date values also can create groups of values just by changing the format. If you want to get an average balance for a quarter use any of the report or summary functions with the date variable and change the format to yyQ. Or a calendar summary by using applying the YEAR format to the same report.