/*
|| Hi
|| How do I create year to date growth from
|| And I need to calculate the year to data growth
*/
DATA employee;
INFORMAT DATE MMDDYY10. employees 3.
;
INPUT DATE employees;
FORMAT DATE DATE9.;
DATALINES;
12/31/2014 60
01/31/2015 70
02/28/2015 100
03/31/2015 131
04/30/2015 140
05/31/2015 150
06/30/2015 160
07/31/2015 170
08/31/2015 145
09/30/2015 150
10/31/2015 153
11/30/2015 160
12/31/2015 170
01/31/2016 180
;
run;
Output:
Date Employees GrowthYTO
01/31/2015 70 10
02/28/2015 100 40
03/31/2015 131 71
04/30/2015 140 80
05/31/2015 150 90
06/30/2015 160 100
07/31/2015 170 110
08/31/2015 145 95
09/30/2015 150 100
10/31/2015 153 103
11/30/2015 160 110
12/31/2015 170 120
01/31/2016 180 130
Best regards
Terkel
Thank you for being so kind to take the time to answer my questions.
Best regards
Terkel
It seems that, starting with 08/31/2015, you calculate with an initial value of 50. Why do you do that?
Aside from that, the calculating data step would use a simple retain:
DATA employee;
INFORMAT DATE MMDDYY10. employees 3.
;
INPUT DATE employees;
FORMAT DATE DATE9.;
DATALINES;
12/31/2014 60
01/31/2015 70
02/28/2015 100
03/31/2015 131
04/30/2015 140
05/31/2015 150
06/30/2015 160
07/31/2015 170
08/31/2015 145
09/30/2015 150
10/31/2015 153
11/30/2015 160
12/31/2015 170
01/31/2016 180
;
run;
%let start='31dec2014'd;
data want;
set employee;
retain init_count;
if init_count ne . then growthyto = employees - init_count;
if date = &start then init_count = employees;
if growthyto ne .;
drop init_count;
run;
Well, embarrassing !!!!, I’m afraid I forgot that I need to calculate the year-to-date growh for each year. Is that possible?
Just replace
if date = &start then init_count = employees;
with
if month(date) = 12 then init_count = employees;
and the count will start anew for each year.
Personally I don't do growth calculations, so I may have taken this to simplistic, but what about something like:
DATA employee; informat date mmddyy10. employees 3.; input date employees; format date date9.; datalines; 12/31/2014 60 01/31/2015 70 02/28/2015 100 03/31/2015 131 04/30/2015 140 05/31/2015 150 06/30/2015 160 07/31/2015 170 08/31/2015 145 09/30/2015 150 10/31/2015 153 11/30/2015 160 12/31/2015 170 01/31/2016 180 ; run; data want; set employee; retain growthyto; tmp=employees-lag(employees); growthyto=sum(growthyto,tmp); run;
Note, that starting here:
Your output doesn't match my understanding as I get 85?
08/31/2015 145 95
Right! My mistake. Sorry for the confusion, Thanks for the elegant solution. What If I need to calculate the growth for each year?
Best regards
Terkel
That depends on the definition of a year, if its just the yearpart of the date then that is simple, have another variable year which is set to yearpart(date), and then by group:
data employee; informat date mmddyy10. employees 3.; input date employees; format date date9.;
yr=yearpart(date); datalines; 12/31/2014 60 01/31/2015 70 02/28/2015 100 03/31/2015 131 04/30/2015 140 05/31/2015 150 06/30/2015 160 07/31/2015 170 08/31/2015 145 09/30/2015 150 10/31/2015 153 11/30/2015 160 12/31/2015 170 01/31/2016 180 ; run; data want; set employee;
by year;
if first.year then growthyto=0; retain growthyto; tmp=employees-lag(employees); growthyto=sum(growthyto,tmp); run;
If you need an exact year from a date, then do a datastep first, and assign a year group variable to the data and then the above.
Thank you for being so kind to take the time to answer my questions.
Best regards
Terkel
Hi,
Glad to help. Just to note, its generally a good idea to mark the post of the solution as the solution, not just to reward the person giving the advice, but also to ensure the actual solution appears under the post for others searching for the same thing.
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.