Calcite | Level 5

## Change in Revenue for Partial Months in a year

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: Change in Revenue for Partial Months in a year

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;

set have;
by companyNo year date;

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

run;

data YoY;
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,

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;``````

4 REPLIES 4
Super User

## Re: Change in Revenue for Partial Months in a year

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?
Calcite | Level 5

## Re: Change in Revenue for Partial Months in a year

Hi Reeza,

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;``````
Super User

## Re: Change in Revenue for Partial Months in a year

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;

set have;
by companyNo year date;

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

run;

data YoY;
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,

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;``````

Calcite | Level 5

## Re: Change in Revenue for Partial Months in a year

Reeza,

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

Discussion stats
• 4 replies
• 855 views
• 0 likes
• 2 in conversation