Change in Revenue for Partial Months in a year

[Chugvenk]

Hi,

I am new to SAS and need some help. I have a dataset with the following data: Company number, year, month, Revenue. File attached. I was able to calculate the change in revenue for current month compared to previous month using the below code

data mylibrary.RevenueYOYGrowth;

     set mylibrary.chug_test;

     by company_number yr;

     lyr1 = lag1(revenue);

     if first.yr then count=0;else count+1;

     if count ge 1 then growth1yr=100*(revenue-lyr1)/lyr1;

run;

The above code works perfectly if the company has data for all the months in year.

Requirement is, if the company has partial revenue in one year, the change in revenue should only be calculated for those months.

So if a company has revenue for months say September 2017, October 2017, November 2017, December 2017, then the revenue should be calculated only for those months in 2018 also.

Basically, (sum(september2017+october2017+november2017+december2017)-sum(september2018+october2018+november2018+december2018))/sum(september2018+october2018+november2018+december2018)

Excel attached shows sample data for both scenarios. I am needing help for the scenario in yellow. Please find attached

Appreciate a quick response! Thank you!

[Reeza]

This is know as a YTD comparison and is quite common. A lot matters on your data structure, but I can't download files or attachments. Can you post a different example of your data?

[Chugvenk]

Hi Reeza, 

Thanks for your response

This is the desired output

CompanyNo	Year	Month	Rev
123	2017	September	250
123	2017	October	165
123	2017	November	170
123	2017	December	150
123	2018	January	125
123	2018	February	150
123	2018	March	175
123	2018	April	200
123	2018	May	225
123	2018	June	250
123	2018	July	275
123	2018	August	300
123	2018	September	325
123	2018	October	350
123	2018	November	375
123	2018	December	400
		GROWTH	-49.310345

So basically, IF company has revenue for n number of months in a 2017, the same number of months should be compared for 2018.

This is the code I had worked. It works fine if the data exists for all months in a year, so I can simply use the lag function to see the value for the previous row.

data mylib.RevenueYOYGrowth;

     set mylib.cc_test;

     by client_number yr;

     lyr1 = lag1(revenue);

     if first.yr then count=0;else count+1;

     if count ge 1 then growth1yr=100*(revenue-lyr1)/lyr1;

 

     run;

[Reeza · Accepted solution]

Here's one way to do that calculation:

1. Create a Year to date value that does the cumulative count for each year

2. Use LAG12 to find the previous year so you have the YoY change. 

data have;
infile cards truncover dlm=' ' dsd;
length companyNo $3. month $12.;
input CompanyNo $	Year	Month $	Rev;
date = input("01"||substr(Month, 1,3)||put(year, 4.), date9.);
format date yymm6.;
cards;
123 2017 September 250
123 2017 October 165
123 2017 November 170
123 2017 December 150
123 2018 January 125
123 2018 February 150
123 2018 March 175
123 2018 April 200
123 2018 May 225
123 2018 June 250
123 2018 July 275
123 2018 August 300
123 2018 September 325
123 2018 October 350
123 2018 November 375
123 2018 December 400
;;;;
run;

proc sort data=have; by companyNo date;

data add_CumulativeRev;
set have;
by companyNo year date;

if first.year then cumulative_rev = Rev;
else cumulative_rev + Rev;

run;

data YoY;
set add_cumulativeRev;
by companyNo year date;

if first.companyNo then count=1;
else count+1;

Rev12 = lag12(cumulative_rev);

if count>12 then YoY = cumulative_rev/rev12-1;
 
run;

---

@Chugvenk wrote:

Hi Reeza, 

Thanks for your response

This is the desired output

CompanyNo	Year	Month	Rev
123	2017	September	250
123	2017	October	165
123	2017	November	170
123	2017	December	150
123	2018	January	125
123	2018	February	150
123	2018	March	175
123	2018	April	200
123	2018	May	225
123	2018	June	250
123	2018	July	275
123	2018	August	300
123	2018	September	325
123	2018	October	350
123	2018	November	375
123	2018	December	400
		GROWTH	-49.310345

So basically, IF company has revenue for n number of months in a 2017, the same number of months should be compared for 2018.

This is the code I had worked. It works fine if the data exists for all months in a year, so I can simply use the lag function to see the value for the previous row.

data mylib.RevenueYOYGrowth;

     set mylib.cc_test;

     by client_number yr;

     lyr1 = lag1(revenue);

     if first.yr then count=0;else count+1;

     if count ge 1 then growth1yr=100*(revenue-lyr1)/lyr1;

 

     run;

---

[Chugvenk]

Reeza,

Thank you for your response! I tried it out and it worked. You are awesome!