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!
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;
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;
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;
Reeza,
Thank you for your response! I tried it out and it worked. You are awesome!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.