Identifying Missing Data for All Years in Panel Data

[Farhan Hasnat]

My data is panel data and I am trying to list/identify Tickers that has missing values for ChangeSales for all years. Lastly, I would just drop them from the sample.

I am a newbie in Stata and it would be really helpful if I could get some lead on which commands would come handy so that I could dig on them more.

Thank you in advance

* Example generated by -dataex-. For more info, type help dataex
clear
input str9 Ticker int Year double WC01001 float(id ChangeSales)
"TH:2S"  1993        . 1        .
"TH:2S"  1994        . 1        .
"TH:2S"  1995        . 1        .
"TH:2S"  1996        . 1        .
"TH:2S"  1997        . 1        .
"TH:2S"  1998        . 1        .
"TH:2S"  1999        . 1        .
"TH:2S"  2000        . 1        .
"TH:2S"  2001        . 1        .
"TH:2S"  2002        . 1        .
"TH:2S"  2003        . 1        .
"TH:2S"  2004        . 1        .
"TH:2S"  2005        . 1        .
"TH:2S"  2006  2396580 1        .
"TH:2S"  2007  2967800 1   571220
"TH:2S"  2008  2795130 1  -172670
"TH:2S"  2009  2559971 1  -235159
"TH:2S"  2010  2812507 1   252536
"TH:2S"  2011  3694521 1   882014
"TH:2S"  2012  4058632 1   364111
"TH:2S"  2013  4092036 1    33404
"TH:2S"  2014  4235393 1   143357
"TH:7UP" 1993        . 2        .
"TH:7UP" 1994        . 2        .
"TH:7UP" 1995        . 2        .
"TH:7UP" 1996        . 2        .
"TH:7UP" 1997        . 2        .
"TH:7UP" 1998        . 2        .
"TH:7UP" 1999        . 2        .
"TH:7UP" 2000        . 2        .
"TH:7UP" 2001  4862969 2        .
"TH:7UP" 2002  5379531 2   516562
"TH:7UP" 2003  4710713 2  -668818
"TH:7UP" 2004  5630530 2   919817
"TH:7UP" 2005 10402038 2  4771508
"TH:7UP" 2006 13243907 2  2841869
"TH:7UP" 2007 10404109 2 -2839798
"TH:7UP" 2008  8389721 2 -2014388
"TH:7UP" 2009  5858009 2 -2531712
"TH:7UP" 2010  6337317 2   479308
"TH:7UP" 2011  5979980 2  -357337
"TH:7UP" 2012  5116875 2  -863105
"TH:7UP" 2013  4733706 2  -383169
"TH:7UP" 2014  2589388 2 -2144318
"TH:A"   1993        . 3        .
"TH:A"   1994        . 3        .
"TH:A"   1995        . 3        .
"TH:A"   1996        . 3        .
"TH:A"   1997        . 3        .
"TH:A"   1998        . 3        .
"TH:A"   1999        . 3        .
"TH:A"   2000        . 3        .
"TH:A"   2001        . 3        .
"TH:A"   2002        . 3        .
"TH:A"   2003  1011632 3        .
"TH:A"   2004  1774367 3   762735
"TH:A"   2005   964444 3  -809923
"TH:A"   2006   866661 3   -97783
"TH:A"   2007   799592 3   -67069
"TH:A"   2008   803531 3     3939
"TH:A"   2009  3435956 3  2632425
"TH:A"   2010  3334096 3  -101860
"TH:A"   2011  1546887 3 -1787209
"TH:A"   2012  1778339 3   231452
"TH:A"   2013  1762664 3   -15675
"TH:A"   2014  2438022 3   675358
"TH:A5"  1993        . 4        .
"TH:A5"  1994        . 4        .
"TH:A5"  1995        . 4        .
"TH:A5"  1996        . 4        .
"TH:A5"  1997        . 4        .
"TH:A5"  1998        . 4        .
"TH:A5"  1999        . 4        .
"TH:A5"  2000        . 4        .
"TH:A5"  2001        . 4        .
"TH:A5"  2002    51565 4        .
"TH:A5"  2003   230007 4   178442
"TH:A5"  2004   311785 4    81778
"TH:A5"  2005   269865 4   -41920
"TH:A5"  2006   167860 4  -102005
"TH:A5"  2007   263836 4    95976
"TH:A5"  2008   270199 4     6363
"TH:A5"  2009   311487 4    41288
"TH:A5"  2010   156315 4  -155172
"TH:A5"  2011   413279 4   256964
"TH:A5"  2012   207449 4  -205830
"TH:A5"  2013    92059 4  -115390
"TH:A5"  2014    63733 4   -28326
"TH:AA"  1993   194339 5        .
"TH:AA"  1994   521410 5   327071
"TH:AA"  1995   876779 5   355369
"TH:AA"  1996  2740334 5  1863555
"TH:AA"  1997  6676764 5  3936430
"TH:AA"  1998  9526501 5  2849737
"TH:AA"  1999 13008913 5  3482412
"TH:AA"  2000 14962025 5  1953112
"TH:AA"  2001 15641083 5   679058
"TH:AA"  2002 14766175 5  -874908
"TH:AA"  2003 17729242 5  2963067
"TH:AA"  2004 20780963 5  3051721
end

[Andrew Musau]

Missing values are sorted last. So if the first sorted value is missing and the last is missing, then they are all missing.

bys Ticker (ChangeSales): gen tag= missing(ChangeSales[1]) & missing(ChangeSales[_N])

[Øyvind Snilsberg]

by Ticker (ChangeSales), sort: gen wanted = ChangeSales[1] == .

missing is treated as larger than any non-missing value, so if the smallest value of ChangeSales (i.e. ChangeSales[1]) is missing, ChangeSales is always missing.

[Farhan Hasnat]

Andrew Musau and Øyvind Snilsberg Thank you so much ! The codes worked perfectly for me. I sorted the list and dropped the missing observations accordingly