topic Re: Change in Revenue for Partial Months in a year in SAS Programming

Change in Revenue for Partial Months in a year

Chugvenk — Wed, 01 May 2019 22:24:38 GMT

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!

Re: Change in Revenue for Partial Months in a year

Reeza — Wed, 01 May 2019 22:27:10 GMT

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?

Re: Change in Revenue for Partial Months in a year

Chugvenk — Wed, 01 May 2019 22:38:21 GMT

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;

Re: Change in Revenue for Partial Months in a year

Reeza — Thu, 02 May 2019 15:19:55 GMT

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;

Re: Change in Revenue for Partial Months in a year

Chugvenk — Thu, 09 May 2019 03:20:23 GMT

Reeza,

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