How to do calculations with a lag

[Saleh sharmah]

Dear members
I’m trying to creat new variable_ sales growth calculated as ( current year sales - last year sales)/ last year sales.

kindly, can anyone provide me with the code in calculating the above wanted variable?

thanks in advance

[Andrew Musau]

Do you have panel data? If so, xtset the data and use time-series operators.

xtset firm year
gen wanted= (sales-L.sales)/L.sales

If the value of sales is never zero, you can use log differences to approximate the growth rate.

[Saleh sharmah]

Thanks Andrew. It’s noted

[Saleh sharmah]

Hey Andrew
The code you provided returns a value of zero to sales growth! What’s the problem?

[Andrew Musau]

Zero growth can occur if the lagged value of sales is equal to the current value. If this is not the case, provide a data example using dataex showing such cases.

[Saleh sharmah]

i
Dear Andrew,
please find the data example
input float brorroer_id double log_sales int Year float country_id
2005 5 921 18.69908
2006 39 176 18.69908
2009 1 47 18.69908
2009 1 47 18.69908
2009 38 111 18.69908
2009 38 111 18.69908
2010 38 365 18.69908
2010 38 913 18.69908
2011 1 736 18.69908
2011 38 913 18.69908
2011 1 47 18.69908
2011 38 365 18.69908
2011 38 913 18.69908
2012 38 111 18.69908
2012 38 111 18.69908
2014 38 111 18.69908
2014 38 111 18.69908
2014 38 111 18.69908
2014 38 111 18.69908
2014 38 111 18.69908
2015 38 111 18.69908
2015 38 111 18.69908
2015 38 75 18.69908
2015 38 75 18.69908
2016 33 800 18.69908
2016 33 800 18.69908
2016 38 280 18.69908
2016 38 679 18.69908
2016 7 107 18.69908
2017 17 753 18.69908
2017 13 179 18.69908
2017 1 331 18.69908
2017 16 208 18.69908
2017 17 753 18.69908
2017 30 511 18.69908
2018 1 406 18.69908
2018 33 797 18.69908
2018 38 408 18.69908
2018 39 63 18.69908
2018 15 392 18.69908
2018 39 220 18.69908
2019 17 504 18.69908
2019 17 504 18.69908
2019 17 504 18.69908
2019 17 504 18.69908
2008 38 913 18.70389
2018 17 446 18.70549
2018 17 446 18.70549
2018 17 446 18.70549
2017 38 913 18.7264
2017 38 111 18.73441
2017 38 111 18.73441
2017 38 111 18.73441
2009 38 734 18.74905
2009 38 734 18.74905
2009 38 734 18.74905
2016 38 734 18.76603
2016 38 734 18.76603
2018 33 6 18.76656
2018 33 6 18.76656
2018 17 753 18.8191
2018 17 753 18.8191
2018 17 753 18.8191

[Andrew Musau]

Are these all the variables that you have? Why do you have duplicates?

2018 17 753 18.8191
2018 17 753 18.8191
2018 17 753 18.8191

At the moment you do not have panel data.

[Saleh sharmah]

I have over 1900 variables and for the seek of simplicity I provided you with the key variables of interest. I have declared my data as panel and still the code your provided earlier is not working!

[Saleh sharmah]

I have over 1900 variables and for the seek of simplicity I provided you with the key variables of interest. I have declared my data as panel and still the code your provided earlier is not working!

[Andrew Musau]

I have declared my data as panel and still the code your provided earlier is not working

There is no way you would have successfully done this if your data structure is as in #6. You would have gotten a repeated time values within panel error. The code in #2 requires id and year to uniquely identify an observation (i.e. panel data). Also your variables in #6 are all mixed up. It is apparent that year is the first variable and it is very unlikely that the country identifier includes a fractional part.

. list in 1/5,abbreviate(20)

+---------------------------------------------+
| brorroer_id log_sales Year country_id |
|---------------------------------------------|
1. | 2005 5 921 18.69908 |
2. | 2006 39 176 18.69908 |
3. | 2009 1 47 18.69908 |
4. | 2009 1 47 18.69908 |
5. | 2009 38 111 18.69908 |
+---------------------------------------------+

In any case, as long as sales is constant within a year, you can create a panel dataset, lag sales and merge back with the original. Please provide a new data example using dataex with the proper variable names for code suggestions.

sort brorroer_id Year
dataex brorroer_id log_sales Year country_id

[Nick Cox]

Note that Andrew Musau means exactly what he said in #2. You need to xtset in terms of an identifier and a time variable. Hence, for example, an identifier alone will not help you one bit in terms of what you want to do.

[Saleh sharmah]

I completely understand what both of you are saying. The issue within my dataset is that a firm can have multiple observations in a given year and this is intended for analysis as each observation differs from another within each firm in a given year. I have declared my data as : xtset observations number Year. This is an issue when lagging my variables. Therefore, given my dataset circumstance, is there a way to be able to use the lagging of variables within my dataset?

thanks for your input

[Nick Cox]

In short, no, not with what you are telling us in this thread.

Backing up,

xtset observations number Year

never makes sense in Stata -- and as said if you typed something like

xtset country_id Year

at data like that in #6 Stata would have issued an error message.

It's worse: your data example is completely confused as Andrew Musau pointed out in #10 -- which you have yet to explain.

Without more clarity on your dataset and your goals, it is hard to say more constructively. You need to be clear that in principle you can define a lagged value before you try to work out how to calculate it with Stata code.

[Saleh sharmah]

Thanks Nick for your reply. I need to calculate a new variable: sales growth (as this year sales -last year sales)/last year’s sales. This kind of dataset is common within my research so I expect a way of estimating the sales growth.

[Nick Cox]

That's what the thread started with, but I don't know what to suggest beyond using collapse to get single observations for each identifier and year.