Dear all,

I am desperately looking for your help. I am trying to reshape my data from long to wide format using the rolling windows (over the past 5 years).

market indicates the ID of the market the focal firm entered at T.

What I am trying to here is to restructure the dataset like the table below while creating marketN variables that indicate which market(s) the focal firm entered over the past 5 years:
So, marketN variables should be created depending on the total number of markets the firm entered over the past 5 years (e.g., if the firm entered 15 markets in total, 15 marketN variables from market1 to market15 should be created).

firm year market1 market2 market3 market4 market5
40 2011 9 . . . .
40 2012 9 25 . . .
40 2014 9 25 10 . .
As of 2011, Firm 40 entered one market (ID: 9) over the past 5 years.
As of 2012, Firm 40 entered two markets (ID: 9, 25) over the past 5 years.
As of 2014, Firm 40 entered three markets (ID: 9, 25, 10) over the past 5 years.

Original data in a long format looks like this:
input float(firm year market)
40 2010 9
40 2011 9
40 2011 25
40 2012 10
40 2014 9
40 2014 10
40 2015 9

It's not difficult to reshape the dataset from long to wide format if I only look at the given year T, but I have not been able to come up with a clever way to restructure my data like the table above.
I would be grateful if anyone can help me. I am very looking forward to your reply.


Thank you in advance for your help.


Best regards,


Anna




Code:
* Example generated by -dataex-. To install: ssc install dataex
clear
input float(firm year market)
  40 2010  9
  40 2011  9
  40 2011 25
  40 2012 10
  40 2014  9
  40 2014 10
  40 2015  9
 965 2007  5
 965 2007 15
 965 2008  3
 965 2009  1
 965 2009  6
 965 2009 15
 965 2009 18
 967 1994  7
 967 1995  1
 967 1995 15
 967 1996 15
 967 1997  3
 967 1997  9
 967 2002  1
1038 1983 15
1038 1985  6
1038 1985  9
1038 1985 15
1038 1986  3
1038 1987  6
1038 1992  6
1084 2001  3
1084 2008  6
1084 2010  6
1084 2010  9
1084 2011  6
1084 2011  9
1084 2012  7
1127 1999  6
1127 1999  7
1127 2002  6
1127 2002  7
1127 2004  3
1127 2010  7
1127 2015  5
1463 1968 21
1463 1970  7
1463 1975  1
1463 1976  7
1463 1976 21
1463 1977  7
1463 1978  9
1472 1961  3
1472 1963  3
1472 1964  1
1472 1965  6
1472 1967  3
1472 1971  9
1472 2001 27
1558 1929  1
1558 1993  5
1558 1997  9
1558 2001  9
1558 2005  9
1558 2009  9
1558 2013  9
1729 2003  9
1729 2004  9
1729 2006  9
1729 2009  9
1729 2010  9
1729 2011  1
1729 2013  9
2440 1985  1
2440 1986  1
2440 1986  6
2440 1989  6
2440 1990  1
2440 1991  1
2440 1996  6
2544 1937  6
2544 1940  6
2544 1940  9
2544 1955  6
2544 1955  9
2544 1956  6
2544 1956  9
2652 1979  1
2652 1980  1
2652 1981  6
2652 1982  7
2652 1983  1
2652 1983  5
2652 1993  1
2694 1965 11
2694 1967 15
2694 1968  7
2694 1977  6
2694 1977  7
2694 1977 15
2694 1979 15
2775 2004  7
2775 2006  1
end