Obsidian | Level 7

## percentage change in value over multiple years for different firms

data growth;
input firm sales year

datalines;
A 2400 2001
A 2500 2002
A 2600 2003
A 2700 2004
A 2800 2005
B 1500 2001
B 1600 2002
B 1700 2003
B 1800 2004
B 1900 2005
C 3100 2001
C 3200 2002
C 3300 2003
C 3400 2004
C 3400 2005

;

I need 3 output column under   growth_in_1yr  , growth_in_2yrs   &  growth_in_3yrs
for percentage change in sales over 1,2 and 3 years for each firm and each year.

in other words, how much the sales changed(%) in 1 yr , 2 yr & 3 yr in separate column for each firm on each year.

But the problem is data always overlaps with other firms. I am new in SAS and programming. So its really confusing for me.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: percentage change in value over multiple years for different firms

See if this gets you started;

```data work.growth;
input firm \$ sales year;
datalines;
A 2400 2001
A 2500 2002
A 2600 2003
A 2700 2004
A 2800 2005
B 1500 2001
B 1600 2002
B 1700 2003
B 1800 2004
B 1900 2005
C 3100 2001
C 3200 2002
C 3300 2003
C 3400 2004
C 3400 2005
;

data want;
set work.growth;
by firm;
lyr1 = lag1(sales);
lyr2 = lag2(sales);
lyr3 = lag3(sales);
if first.firm then count=0;
else count+1;
if count ge 1 then growth1yr = (sales - lyr1)/lyr1;
if count ge 2 then growth2yr = (sales - lyr2)/lyr2;
if count ge 3 then growth3yr = (sales - lyr3)/lyr3;
run;

```

This assumes that you data is sorted by FIRM and YEAR. Note the changes to your data step code to remove the errors.

LAG is a function to look at a previous value. The COUNT variable is reset each time the firm changes and indicates how many previous years may be available for use. I left the LYR1 etc variables in the data so you could see which ones are used in calculations.

4 REPLIES 4
Super User

## Re: percentage change in value over multiple years for different firms

Ok, That's what you have. What do you want as output? Please post your data as text, not images. Otherwise to work with it we need to type it in, and that's a lot less people who will then work on your problem.

Obsidian | Level 7

## Re: percentage change in value over multiple years for different firms

i edited the post. is it better now ?

Super User

## Re: percentage change in value over multiple years for different firms

I think you may have missed the part about what you need as output?

Super User

## Re: percentage change in value over multiple years for different firms

See if this gets you started;

```data work.growth;
input firm \$ sales year;
datalines;
A 2400 2001
A 2500 2002
A 2600 2003
A 2700 2004
A 2800 2005
B 1500 2001
B 1600 2002
B 1700 2003
B 1800 2004
B 1900 2005
C 3100 2001
C 3200 2002
C 3300 2003
C 3400 2004
C 3400 2005
;

data want;
set work.growth;
by firm;
lyr1 = lag1(sales);
lyr2 = lag2(sales);
lyr3 = lag3(sales);
if first.firm then count=0;
else count+1;
if count ge 1 then growth1yr = (sales - lyr1)/lyr1;
if count ge 2 then growth2yr = (sales - lyr2)/lyr2;
if count ge 3 then growth3yr = (sales - lyr3)/lyr3;
run;

```

This assumes that you data is sorted by FIRM and YEAR. Note the changes to your data step code to remove the errors.

LAG is a function to look at a previous value. The COUNT variable is reset each time the firm changes and indicates how many previous years may be available for use. I left the LYR1 etc variables in the data so you could see which ones are used in calculations.

Discussion stats
• 4 replies
• 3038 views
• 0 likes
• 3 in conversation