Creating a new variable that takes on a certain value on an id-level based on values on a more detailed level

[Jonas Palmstrom]

I have an id variable (product), a time variable (month), a revenue variable (sales) and a lot of other variables

I want to make a new variable with info on product sales in the final month. I want this variable to have the value of the sales of a product in the final month, but I want it to also apply for the same product in different months. That is, this new var needs to only change value when id is changing value. Meaning all the same id's (products) needs to have the same respective values on this new var.

What commands should I use?

[William Lisowski]

Welcome to Statalist, Jonas.

The following should do what you want.

by product (month), sort : generate last_sales = sales[_N]

[Jonas Palmstrom]

Hi William, thank you very much.

Should I be able to copy/paste that code right in? Because it doesn't work.

I want the variable to reflect the sales in the last month, and I want that variable to have the same value across all observations on this product (also observations in different months).

[Jonas Palmstrom]

I found the solution now.

gen sales_final_month = sales_product_month if month == "2017_03"

bysort product (sales_final_month): replace sales_final_month = sales_final_month[1]

[William Lisowski]

Well, my code in post #2 won't work because in post #1 you said you had a variable "sales" but your post #4 shows that the variable is "sales_product_month". If you actually just copied and pasted my code from post #2 without trying to understand what it is doing and then altering it to match your actual data, it wasn't going to work.

For better luck with future questions, please review the Statalist FAQ linked to from the top of the page, as well as from the Advice on Posting link on the page you used to create your post. Note especially sections 9-12 on how to best pose your question. The more you help others understand your problem, the more likely others are to be able to help you solve your problem.

For this problem, it would have been helpful to post a small hand-made example, perhaps with just the necessary variables and a few observations, for two or three products. In particular, please read FAQ #12 and use dataex and CODE delimiters when posting to Statalist.

And when you describe a problem, section 12.1 is particularly pertinent

12.1 What to say about your commands and your problem

Say exactly what you typed and exactly what Stata typed (or did) in response. N.B. exactly!
...
Never say just that something "doesn't work" or "didn't work", but explain precisely in what sense you didn't get what you wanted.

Finally, it appears that your dates are stored as strings. That will likely be a problem as you proceed with your analysis. You will want to follow the instructions for converting your string variable to a SIF monthly date variable following the guidance in help datetime, which is without a doubt the most visited documentation on my system, with the second-most-visited being Chapter 24 (Working with dates and times) of the Stata User's Guide PDF available from the PDF Documentation item on Stata's Help menu. Before working with dates and times, any Stata user should read the very detailed Chapter 24 thoroughly. After that, the help datetime documentation will usually be enough to point the way. Some people may be able to remember everything without have to continually refer to the documentation, but I for one am not such a person.