BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Gaffron
Fluorite | Level 6

/*

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

1 ACCEPTED SOLUTION

Accepted Solutions
Gaffron
Fluorite | Level 6

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

Best regards

Terkel

 

View solution in original post

8 REPLIES 8
Kurt_Bremser
Super User

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;
Gaffron
Fluorite | Level 6

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

Kurt_Bremser
Super User

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

Gaffron
Fluorite | Level 6

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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. 

Gaffron
Fluorite | Level 6

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

Best regards

Terkel

 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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