Create a new variable that count for the number of observations of a variable for the previous year

[Najiba Ramdani]

Hello everyone,

I have trouble creating a new variable with my data.
In my data every event_date correspond to a layoff announcement made by a firm. I would like to create a variable that count for the number of downsizing operations announced by firms within the same industry (SIC) for the previous year. I would like different firms to belong to same industry if the first 2 digit of their SIC code is the same.

(1) First I would like to create a new variable that only keep the first to digit of the sic but since SIC is numeric I don’t manage to use the “substr” command
(2A) Then was thinking of using the command above to count the number of announcement made by firm belonging to the same 2 digit-SIC industry each year)

• bysort YEAR SIC2: gen MIM=_N

(where the variable SIC2 correspond to the first two digit of the variable SI))
However I don’t know how to continue lag the variable to the next year.
I tried the command

• Gen lagMIM = MIM[_n-1]

But it doesn’t seem to work

This is my first first post on STATA and I made my best to follow the recommendations. However any advice to improve my requests formulation is very welcomed.
Best regards,
N. Ramdani

* Example generated by -dataex-. To install: ssc install dataex
clear
input str54 name str12 isin int(day date event_day YEAR SIC)
"ERAMET - MARKET VALUE"                                  "FR0000131757"  87 18211 18211 2009 1021
"BOURBON CORP. - MARKET VALUE"                           "FR0004548873"  87 18024 18024 2009 1389
"ROCAMAT DEAD - 12/12/14 - MARKET VALUE"                 "FR0000064255"  87 17898 17898 2009 1411
"IMERYS - MARKET VALUE"                                  "FR0000120859"  87 17924 17924 2009 1422
"DANONE - MARKET VALUE"                                  "FR0000120644"  87 18074 18074 2009 2023
"BONDUELLE - MARKET VALUE"                               "FR0000063935"  87 17939 17939 2009 2033
"OENEO - MARKET VALUE"                                   "FR0000052680"  87 17933 17933 2009 2499
"LAGARDERE GROUPE - MARKET VALUE"                        "FR0000130213"  87 18039 18039 2009 2731
"PCAS - MARKET VALUE"                                    "FR0000053514" 130 18003 18003 2009 2834
"L'OREAL - MARKET VALUE"                                 "FR0000120321"  87 17983 17983 2009 2844
"TOTAL - MARKET VALUE"                                   "FR0000120271"  87 17966 17966 2009 2911
"MICHELIN - MARKET VALUE"                                "FR0000121261"  87 18066 18066 2009 3011
"NEXANS - MARKET VALUE"                                  "FR0000044448"  87 18233 18233 2009 3357
"NEXANS - MARKET VALUE"                                  "FR0000044448"  87 18233 18233 2009 3357
"EXEL INDUSTRIES - MARKET VALUE"                         "FR0004527638"  87 18074 18074 2009 3563
"MECELEC COMPOSITES - MARKET VALUE"                      "FR0000061244"  87 18211 18211 2009 3613
"SEB - MARKET VALUE"                                     "FR0000121709"  87 17940 17940 2009 3634
"THALES - MARKET VALUE"                                  "FR0000121329"  87 18079 18079 2009 3663
"NOVATECH INDUSTRIES B - MARKET VALUE"                   "FR0000185464"  87 17920 17920 2009 3679
"PEUGEOT - MARKET VALUE"                                 "FR0000121501"  87 17937 17937 2009 3711
"BENETEAU - MARKET VALUE"                                "FR0000035164"  87 17996 17996 2009 3732
"TRIGANO - MARKET VALUE"                                 "FR0005691656"  87 17913 17913 2009 3792
"PLSTQ.DU VAL DE LOIRE - MARKET VALUE"                   "FR0013252186"  87 17981 17981 2009 3999
"AIR FRANCE-KLM - MARKET VALUE"                          "FR0000031122"  87 18144 18144 2009 4512
"INDEX MULTIMEDIA DEAD - 08/01/15 - MARKET VALUE"        "FR0004061513"  87 18086 18086 2009 4812
"NEXTRADIOTV DEAD - 08/02/16 - MARKET VALUE"             "FR0010240994"  87 17941 17941 2009 4832
"SAINT GOBAIN - MARKET VALUE"                            "FR0000125007"  87 17912 17912 2009 5039
"DEVERNOIS - MARKET VALUE"                               "FR0000060840"  88 17947 17947 2009 5621
"GROUPE FLO - MARKET VALUE"                              "FR0004076891"  87 17962 17962 2009 5812
"EURAZEO - MARKET VALUE"                                 "FR0000121121"  87 18023 18023 2009 6799
"ACCOR - MARKET VALUE"                                   "FR0000120404"  87 18078 18078 2009 7011
"CLUB MEDITERRANEE DEAD - 23/03/15 - MARKET VALUE"       "FR0000121568"  87 18074 18074 2009 7011
"ALTEN - MARKET VALUE"                                   "FR0000071946"  87 18200 18200 2009 7373
"INFOVISTA (OTC) DEAD - DELIST.13/11/12 - MARKET VALUE"  "FR0004031649"  87 17918 17918 2009 7373
"ATOS - MARKET VALUE"                                    "FR0000051732"  87 17946 17946 2009 7373
"ALTRAN TECHNOLOGIES - MARKET VALUE"                     "FR0000034639"  87 18066 18066 2009 8711
"TELEPERFORMANCE - MARKET VALUE"                         "FR0000051807"  87 18056 18056 2009 8742
"SOITEC - MARKET VALUE"                                  "FR0013227113"  87 17989 17989 2009    .
"LDC - MARKET VALUE"                                     "FR0013204336"  87 18374 18374 2010 2015
"REMY COINTREAU - MARKET VALUE"                          "FR0000130395"  87 18298 18298 2010 2085
"NICOX - MARKET VALUE"                                   "FR0013018124"  87 18569 18569 2010 2834
"ALSTOM - MARKET VALUE"                                  "FR0010220475"  87 18539 18539 2010 3511
"DIGIGRAM - MARKET VALUE"                                "FR0000035784"  87 18295 18295 2010 3571
"ACTIA GROUP - MARKET VALUE"                             "FR0000076655"  87 18560 18560 2010 3694
"ADP - MARKET VALUE"                                     "FR0010340141"  87 18337 18337 2010 4581
"GROUPE GORGE - MARKET VALUE"                            "FR0000062671"  87 18326 18326 2010 5084
"AVENIR TELECOM - MARKET VALUE"                          "FR0000066052"  87 18295 18295 2010 5731
"TECHNICOLOR - MARKET VALUE"                             "FR0010918292"  87 18448 18448 2010 7822
"VALLOUREC - MARKET VALUE"                               "FR0000120354"  87 18933 18933 2011 1389
"ARKEMA - MARKET VALUE"                                  "FR0010313833"  87 18946 18946 2011 2819
"LAFARGEHOLCIM - MARKET VALUE"                           "CH0012214059"  87 18772 18772 2011 3241
"DANE-ELEC MEMORY DEAD - DELIST.03/11/17 - MARKET VALUE" "FR0000036774"  96 18700 18687 2011 3572
"DANE-ELEC MEMORY DEAD - DELIST.03/11/17 - MARKET VALUE" "FR0000036774"  87 18700 18700 2011 3572
"ATARI - MARKET VALUE"                                   "FR0010478248"  87 18736 18736 2011 3944
"ATARI - MARKET VALUE"                                   "FR0010478248"  87 18736 18736 2011 3944
"CASINO GUICHARD-P - MARKET VALUE"                       "FR0000125585"  87 18961 18961 2011 5411
"GENERIX GROUP FRANCE - MARKET VALUE"                    "FR0010501692"  87 18954 18954 2011 7371
"RAMSAY GEN SANTE - MARKET VALUE"                        "FR0000044471"  87 18710 18710 2011 8062
"BOUYGUES - MARKET VALUE"                                "FR0000120503"  87 19177 19177 2012 1611
"LAFUMA - MARKET VALUE"                                  "FR0000035263"  87 19355 19355 2012 2329
"SANOFI - MARKET VALUE"                                  "FR0000120578"  87 19261 19261 2012 2834
"ARCHOS - MARKET VALUE"                                  "FR0000182479"  87 19303 19303 2012 3651
"ALCATEL-LUCENT DEAD - 02/11/16 - MARKET VALUE"          "FR0000130007"  87 19284 19284 2012 3661
"RENAULT - MARKET VALUE"                                 "FR0000131906"  87 19303 19303 2012 3711
"FAURECIA - MARKET VALUE"                                "FR0000121147"  87 19309 19309 2012 3714
"ADP - MARKET VALUE"                                     "FR0010340141"  87 19351 19351 2012 4581
"PIERRE & VACANCES - MARKET VALUE"                       "FR0000073041"  87 19284 19284 2012 7011
"PIERRE & VACANCES - MARKET VALUE"                       "FR0000073041"  87 19284 19284 2012 7011
"OVERLAP GROUPE DEAD - 24/12/14 - MARKET VALUE"          "FR0010759530"  87 19093 19093 2012 7372
"VEOLIA ENVIRON - MARKET VALUE"                          "FR0000124141"  87 19313 19313 2012    .
"AIR LIQUIDE - MARKET VALUE"                             "FR0000120073"  87 19507 19507 2013 2813
"L'OREAL - MARKET VALUE"                                 "FR0000120321"  87 19681 19681 2013 2844
"MICHELIN - MARKET VALUE"                                "FR0000121261"  87 19526 19526 2013 3011
"NEXANS - MARKET VALUE"                                  "FR0000044448"  87 19646 19646 2013 3357
"AIR FRANCE-KLM - MARKET VALUE"                          "FR0000031122"  87 19570 19570 2013 4512
"CAFOM - MARKET VALUE"                                   "FR0010151589"  87 19536 19536 2013 5712
"SODEXO - MARKET VALUE"                                  "FR0000121220"  87 19548 19548 2013 5812
"INSIDE SECURE - MARKET VALUE"                           "FR0010291245"  87 19423 19423 2013 7372
"AIRBUS - MARKET VALUE"                                  "NL0000235190"  87 19701 19701 2013 8741
"ERAMET - MARKET VALUE"                                  "FR0000131757"  87 20009 20009 2014 1021
"VALLOUREC - MARKET VALUE"                               "FR0000120354"  87 19857 19857 2014 1389
"BONDUELLE - MARKET VALUE"                               "FR0000063935"  87 20031 20031 2014 2033
"PERNOD-RICARD - MARKET VALUE"                           "FR0000120693"  87 19892 19892 2014 2085
"KINDY DEAD - DELIST.04/09/17 - MARKET VALUE"            "FR0000052904"  87 19984 19984 2014 2252
"DAMARTEX - MARKET VALUE"                                "FR0000185423"  87 19835 19835 2014 2339
"SEQUANA SUSP - SUSP.08/03/19 - MARKET VALUE"            "FR0011352590"  87 19823 19823 2014 2678
"IPSEN - MARKET VALUE"                                   "FR0010259150"  87 19942 19942 2014 2834
"ALSTOM - MARKET VALUE"                                  "FR0010220475"  87 19828 19828 2014 3511
"THALES - MARKET VALUE"                                  "FR0000121329"  87 19753 19753 2014 3663
"PEUGEOT - MARKET VALUE"                                 "FR0000121501"  87 19753 19753 2014 3711
"TRIGANO - MARKET VALUE"                                 "FR0005691656"  87 19829 19829 2014 3792
"XPO LOGISTICS EUROPE - MARKET VALUE"                    "FR0000052870"  87 20076 20076 2014 4212
"XPO LOGISTICS EUROPE - MARKET VALUE"                    "FR0000052870"  25 20076 20164 2014 4212
"TF1 (TV.FSE.1) - MARKET VALUE"                          "FR0000054900"  87 19990 19990 2014 4833
"SAINT GOBAIN - MARKET VALUE"                            "FR0000125007"  87 19977 19977 2014 5039
"CARREFOUR - MARKET VALUE"                               "FR0000120172"  87 19843 19843 2014 5411
"SOCIETE GENERALE - MARKET VALUE"                        "FR0000130809"  87 19766 19766 2014 6029
"EURASIA GROUPE - MARKET VALUE"                          "FR0010844001"  87 19841 19841 2014 6513
"IMERYS - MARKET VALUE"                                  "FR0000120859"  87 20124 20124 2015 1422
"EIFFAGE - MARKET VALUE"                                 "FR0000130452"  87 20212 20212 2015 1541
end

[Clyde Schechter]

//  GET FIRST TWO DIGITS OF SIC CODE
gen int SIC2 = floor(SIC/100)

rangestat (count) wanted = event_day, by(SIC2) interval(YEAR -1 -1)

-rangestat- is written by Robert Picard, Nick Cox, and Roberto Ferrer. It is available from SSC.

Note that in your example data, for many combinations of SIC2 and YEAR there are no observations from the immediately preceding year, so that wanted is often a missing value. Depending on how you will use this variable, you may want to -replace- those missing values with zero.

[Mike Lacy]

First, regarding how to ask a question on StataList. Re-reading the FAQ would help you. Item 12.1 is of particular relevance: Saying "it doesn’t seem to work" gives almost no information and therefore doesn't help us help you.

That aside: I think the following will do what you want. However, note carefully my comments below on the observations with _merge ==1 and _merge == 2.

gen int SIC2 = floor(SIC/100)
// Create a file with the count interest for each year, which will
// be named and marked as "year minus 1" for later merge.
preserve
rename YEAR yearm1
bysort SIC2 yearm1: gen int MIM = _N
by SIC2 yearm1: keep if _n == 1 // only need 1 observations with each total
tempfile temp
keep SIC2 yearm1 MIM
save `temp'
//
restore // original
// Merge the year totals onto original file
gen yearm1 = YEAR - 1
merge m:1 yearm1 SIC2 using `temp', keepusing(MIM)
//
// Check out anomalies
// _merge == 2 observations have values of SIC2 that *did* occur in the prior year,
// but don't occur in the current year.  These would be SIC2 codes that have
// no layoffs in the current year but did have them in the prior year.  I'm
// guessing these would be irrelevant?
list SIC2 YEAR yearm1 if _merge == 2
// drop if _merge == 2 ??
//
// _merge = 1 are observations for which there were no prior year observations, i.e.
// no layoffs in that SIC2 in the prior year?
list SIC2 YEAR yearm1 if _merge == 1
// observations in the same SIC2, so *perhaps* these are true 0s.
// generate MIM = 0 if (_merge ==1)  ??

[Najiba Ramdani]

Thank you very much, i managed to create the variable. I will probably change the missing value by 0.

Thanks a lot,
Best regards
Najiba