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.
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.
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.
i edited the post. is it better now ?
I think you may have missed the part about what you need as output?
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.
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!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.