DATA Step, Macro, Functions and more

Calculationg year to date growth

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 5
Accepted Solution

Calculationg year to date growth

/*

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


Accepted Solutions
Solution
‎03-01-2016 06:14 AM
Occasional Contributor
Posts: 5

Re: Calculationg year to date growth

Thank you for being so kind to take the time to answer my questions.

Best regards

Terkel

 

View solution in original post


All Replies
Super User
Posts: 7,766

Re: Calculationg year to date growth

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 5

Re: Calculationg year to date growth

Posted in reply to KurtBremser

Well, embarrassing !!!!, I’m afraid I forgot that I need to calculate the year-to-date growh for each year. Is that possible?

Super User
Posts: 7,766

Re: Calculationg year to date growth

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Super User
Posts: 7,942

Re: Calculationg year to date growth

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

Occasional Contributor
Posts: 5

Re: Calculationg year to date growth

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

Super User
Super User
Posts: 7,942

Re: Calculationg year to date growth

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. 

Solution
‎03-01-2016 06:14 AM
Occasional Contributor
Posts: 5

Re: Calculationg year to date growth

Thank you for being so kind to take the time to answer my questions.

Best regards

Terkel

 

Super User
Super User
Posts: 7,942

Re: Calculationg year to date growth

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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 365 views
  • 0 likes
  • 3 in conversation