Generating row identifiers for nested panels

[Shekhar Misra]

I am using Stata 15.1. I have nested panel in my data. I am struggling to generate panel row ids. Below is the structure of my data. I want to generate the last column. Can someone suggest how to go about it.

Best, Shekhar

comp_id	year	sub_panel_id	panel_id	new_id
1209	1995	.	1	-18
1209	1996	.	2	-17
1209	1997	.	3	-16
1209	1998	.	4	-15
1209	1999	.	5	-14
1209	2000	.	6	-13
1209	2001	.	7	-12
1209	2002	.	8	-11
1209	2003	.	9	-10
1209	2004	.	10	-9
1209	2005	.	11	-8
1209	2006	.	12	-7
1209	2007	.	13	-6
1209	2008	.	14	-5
1209	2009	.	15	-4
1209	2010	.	16	-3
1209	2011	.	17	-2
1209	2012	.	18	-1
1209	2013	0	19	0
1209	2014	1	20	1
1209	2015	2	21	2
1209	2016	3	22	3
1209	2017	4	23	4
1209	2018	.	24	5

[Andrew Musau]

Please use dataex in the future to present data examples. It appears that you want a counter for observations following the first non-missing value. Here is one way:

* Example generated by -dataex-. To install: ssc install dataex
clear
input float(comp_id year sub_panel_id panel_id new_id)
1209 1995 .  1 -18
1209 1996 .  2 -17
1209 1997 .  3 -16
1209 1998 .  4 -15
1209 1999 .  5 -14
1209 2000 .  6 -13
1209 2001 .  7 -12
1209 2002 .  8 -11
1209 2003 .  9 -10
1209 2004 . 10  -9
1209 2005 . 11  -8
1209 2006 . 12  -7
1209 2007 . 13  -6
1209 2008 . 14  -5
1209 2009 . 15  -4
1209 2010 . 16  -3
1209 2011 . 17  -2
1209 2012 . 18  -1
1209 2013 0 19   0
1209 2014 1 20   1
1209 2015 2 21   2
1209 2016 3 22   3
1209 2017 4 23   4
1209 2018 . 24   5
end

bys comp_id (year): gen wanted= sum(sub_panel_id)
bys comp_id (year): egen total = total(wanted==0 & sub_panel_id==.)
bys comp_id (year): replace wanted= -total+ _n-1
assert wanted==new_id

Result:

. l, clean

       comp_id   year   sub_pa~d   panel_id   new_id   wanted   total  
  1.      1209   1995          .          1      -18      -18      18  
  2.      1209   1996          .          2      -17      -17      18  
  3.      1209   1997          .          3      -16      -16      18  
  4.      1209   1998          .          4      -15      -15      18  
  5.      1209   1999          .          5      -14      -14      18  
  6.      1209   2000          .          6      -13      -13      18  
  7.      1209   2001          .          7      -12      -12      18  
  8.      1209   2002          .          8      -11      -11      18  
  9.      1209   2003          .          9      -10      -10      18  
 10.      1209   2004          .         10       -9       -9      18  
 11.      1209   2005          .         11       -8       -8      18  
 12.      1209   2006          .         12       -7       -7      18  
 13.      1209   2007          .         13       -6       -6      18  
 14.      1209   2008          .         14       -5       -5      18  
 15.      1209   2009          .         15       -4       -4      18  
 16.      1209   2010          .         16       -3       -3      18  
 17.      1209   2011          .         17       -2       -2      18  
 18.      1209   2012          .         18       -1       -1      18  
 19.      1209   2013          0         19        0        0      18  
 20.      1209   2014          1         20        1        1      18  
 21.      1209   2015          2         21        2        2      18  
 22.      1209   2016          3         22        3        3      18  
 23.      1209   2017          4         23        4        4      18  
 24.      1209   2018          .         24        5        5      18

[Shekhar Misra]

Hello Andrew,
This code works perfectly. Thanks a lot. I have noted your point regarding dataex. I will use in the future.

Thanks, Shekhar