topic Calculationg year to date growth in SAS Programming

Calculationg year to date growth

Gaffron — Mon, 29 Feb 2016 09:45:36 GMT

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

Re: Calculationg year to date growth

Kurt_Bremser — Mon, 29 Feb 2016 11:22:37 GMT

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;

Re: Calculationg year to date growth

RW9 — Mon, 29 Feb 2016 11:30:10 GMT

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

Re: Calculationg year to date growth

Gaffron — Mon, 29 Feb 2016 13:43:58 GMT

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

Re: Calculationg year to date growth

Gaffron — Mon, 29 Feb 2016 13:52:14 GMT

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

Re: Calculationg year to date growth

Kurt_Bremser — Mon, 29 Feb 2016 13:56:25 GMT

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.

Re: Calculationg year to date growth

RW9 — Mon, 29 Feb 2016 13:56:48 GMT

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.

Re: Calculationg year to date growth

Gaffron — Tue, 01 Mar 2016 11:14:22 GMT

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

Best regards

Terkel

Re: Calculationg year to date growth

RW9 — Tue, 01 Mar 2016 11:19:23 GMT

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.