Question on Reshaping(?) Data to Create Annual Dummy Variable from Monthly Data

[Josh Weiner]

Hello Everyone,

I have data that runs monthly and each observation is a national troop contribution to a UN peacekeeping mission by month--year and by the type of contribution (troops, observers, police etc). A sample of the data is below so that you can see my variables of interest.

* Example generated by -dataex-. To install: ssc install dataex
clear
input str10 date int year byte month int monthcount str41 contributor long mission1 float(troops1 observers1 monusco tokencontributor)
"1990-11-30" 1990 11 11 "Argentina"  21  29 . . 0
"1990-11-30" 1990 11 11 "Argentina"  32   6 . . 0
"1990-11-30" 1990 11 11 "Argentina"  82   4 . . 0
"1990-11-30" 1990 11 11 "Australia"  36   . . . 0
"1990-11-30" 1990 11 11 "Australia"  39   2 . . 0
"1990-11-30" 1990 11 11 "Australia"  82  13 . . 0
"1990-11-30" 1990 11 11 "Austria"    35 532 . . 0
"1990-11-30" 1990 11 11 "Austria"    36 410 . . 0
"1990-11-30" 1990 11 11 "Austria"    39  11 . . 0
"1990-11-30" 1990 11 11 "Austria"    82  14 . . 0
"1990-11-30" 1990 11 11 "Belgium"    56   2 . . 0
"1990-11-30" 1990 11 11 "Belgium"    82   2 . . 0
"1990-11-30" 1990 11 11 "Bangladesh" 39   5 . . 0
"1990-11-30" 1990 11 11 "Brazil"     21  21 . . 0
"1990-11-30" 1990 11 11 "Brazil"     32   6 . . 0
"1990-11-30" 1990 11 11 "Canada"     21 172 . . 0
"1990-11-30" 1990 11 11 "Canada"     35 230 . . 0
"1990-11-30" 1990 11 11 "Canada"     36 573 . . 0
"1990-11-30" 1990 11 11 "Canada"     39   8 . . 0
"1990-11-30" 1990 11 11 "Canada"     82  19 . . 0
end
label values mission1 mission1
label def mission1 21 "ONUCA", modify
label def mission1 32 "UNAVEM", modify
label def mission1 35 "UNDOF", modify
label def mission1 36 "UNFICYP", modify
label def mission1 39 "UNIIMOG", modify
label def mission1 56 "UNMOGIP", modify
label def mission1 82 "UNTSO", modify

I'm fairly new to Stata, but I would like to know the maximum troop and observer contributions (troops1, observers1) for each country for each mission by year so that I can graph/display the change for a certain mission (variable monusco) over time given the condition that the max value makes the country a token contributor (variable tokencontributor= 1 if troops1<40 & observers1<10).

I'm having trouble isolating the maximum contributions per year or contributions by country by mission by year according to a specific rule like the one for the tokencontributor variable. I thought a dummy variable might be the best way to do this (x= 1 if max value of troops1<40 & observers1<10 for each year) but I'm open to other options. I was hesitant to reshape because I want the max contribution per year rather than the total but perhaps that's an option I'm unaware of?

Note: countries might contribute to a single mission every month of a given year or multiple months of a year, sometimes with different levels of troops or observers. Data runs from 1990-2018.

Thank you in advance for any help you can provide and please let me know if something isn't clear.

[William Lisowski]

Welcome to Statalist.

Thank you for providing your example data using the dataex command. I have used it to build made-up example data to demonstrate my code on, since your example data included only one month. I invented data for just one country, for two missions, for twentyfour months.

I believe the code below will start you in a useful direction.

* Example generated by -dataex-. To install: ssc install dataex
clear
input str10 date str20 contributor float(mission1 troops1)
"1990-01-31" "Canada" 21 246
"1990-02-28" "Canada" 21 231
"1990-03-31" "Canada" 21 246
"1990-04-30" "Canada" 21 167
"1990-05-31" "Canada" 21 162
"1990-06-30" "Canada" 21 223
"1990-07-31" "Canada" 21 197
"1990-08-31" "Canada" 21 185
"1990-09-30" "Canada" 21 199
"1990-10-31" "Canada" 21 151
"1990-11-30" "Canada" 21 217
"1990-12-31" "Canada" 21 237
"1991-01-31" "Canada" 21 231
"1991-02-28" "Canada" 21 191
"1991-03-31" "Canada" 21 207
"1991-04-30" "Canada" 21 155
"1991-05-31" "Canada" 21 158
"1991-06-30" "Canada" 21 175
"1991-07-31" "Canada" 21 169
"1991-08-31" "Canada" 21 204
"1991-09-30" "Canada" 21 162
"1991-10-31" "Canada" 21 168
"1991-11-30" "Canada" 21 162
"1991-12-31" "Canada" 21 217
"1990-01-31" "Canada" 82 207
"1990-02-28" "Canada" 82 248
"1990-03-31" "Canada" 82 165
"1990-04-30" "Canada" 82 245
"1990-05-31" "Canada" 82 191
"1990-06-30" "Canada" 82 246
"1990-07-31" "Canada" 82 155
"1990-08-31" "Canada" 82 245
"1990-09-30" "Canada" 82 203
"1990-10-31" "Canada" 82 188
"1990-11-30" "Canada" 82 152
"1990-12-31" "Canada" 82 239
"1991-01-31" "Canada" 82 208
"1991-02-28" "Canada" 82 218
"1991-03-31" "Canada" 82 212
"1991-04-30" "Canada" 82 177
"1991-05-31" "Canada" 82 227
"1991-06-30" "Canada" 82 213
"1991-07-31" "Canada" 82 199
"1991-08-31" "Canada" 82 235
"1991-09-30" "Canada" 82 203
"1991-10-31" "Canada" 82 205
"1991-11-30" "Canada" 82 161
"1991-12-31" "Canada" 82 159
end
label values mission1 mission1
label def mission1 21 "ONUCA", modify
label def mission1 82 "UNTSO", modify

// convert string date to SIF monthly date
generate dateymd = daily(date,"YMD")
generate dateym = mofd(dateymd)
generate year = yofd(dateymd)
format dateymd %td
format dateym %tm
list in 10/15, clean noobs
drop date

// generate maximum of troops1 by country, mission1, year
bysort contributor mission1 year: egen ytroops = max(troops1)
list contributor mission1 dateym troops1 ytroops, clean noobs abbreviate(12)

 list in 10/15, clean noobs

          date   contri~r   mission1   troops1     dateymd    dateym   year  
    1990-10-31     Canada      ONUCA       151   31oct1990   1990m10   1990  
    1990-11-30     Canada      ONUCA       217   30nov1990   1990m11   1990  
    1990-12-31     Canada      ONUCA       237   31dec1990   1990m12   1990  
    1991-01-31     Canada      ONUCA       231   31jan1991    1991m1   1991  
    1991-02-28     Canada      ONUCA       191   28feb1991    1991m2   1991  
    1991-03-31     Canada      ONUCA       207   31mar1991    1991m3   1991  

. drop date

. 
. // generate maximum of troops1 by contributor, mission1, year
. bysort contributor mission1 year: egen ytroops = max(troops1)

. list contributor mission1 dateym troops1 ytroops, clean noobs abbreviate(12)

    contributor   mission1    dateym   troops1   ytroops  
         Canada      ONUCA    1990m1       246       246  
         Canada      ONUCA    1990m2       231       246  
         Canada      ONUCA    1990m3       246       246  
         Canada      ONUCA    1990m4       167       246  
         Canada      ONUCA    1990m5       162       246  
         Canada      ONUCA    1990m6       223       246  
         Canada      ONUCA    1990m7       197       246  
         Canada      ONUCA    1990m8       185       246  
         Canada      ONUCA    1990m9       199       246  
         Canada      ONUCA   1990m10       151       246  
         Canada      ONUCA   1990m11       217       246  
         Canada      ONUCA   1990m12       237       246  
         Canada      ONUCA    1991m1       231       231  
         Canada      ONUCA    1991m2       191       231  
         Canada      ONUCA    1991m3       207       231  
         Canada      ONUCA    1991m4       155       231  
         Canada      ONUCA    1991m5       158       231  
         Canada      ONUCA    1991m6       175       231  
         Canada      ONUCA    1991m7       169       231  
         Canada      ONUCA    1991m8       204       231  
         Canada      ONUCA    1991m9       162       231  
         Canada      ONUCA   1991m10       168       231  
         Canada      ONUCA   1991m11       162       231  
         Canada      ONUCA   1991m12       217       231  
         Canada      UNTSO    1990m1       207       248  
         Canada      UNTSO    1990m2       248       248  
         Canada      UNTSO    1990m3       165       248  
         Canada      UNTSO    1990m4       245       248  
         Canada      UNTSO    1990m5       191       248  
         Canada      UNTSO    1990m6       246       248  
         Canada      UNTSO    1990m7       155       248  
         Canada      UNTSO    1990m8       245       248  
         Canada      UNTSO    1990m9       203       248  
         Canada      UNTSO   1990m10       188       248  
         Canada      UNTSO   1990m11       152       248  
         Canada      UNTSO   1990m12       239       248  
         Canada      UNTSO    1991m1       208       235  
         Canada      UNTSO    1991m2       218       235  
         Canada      UNTSO    1991m3       212       235  
         Canada      UNTSO    1991m4       177       235  
         Canada      UNTSO    1991m5       227       235  
         Canada      UNTSO    1991m6       213       235  
         Canada      UNTSO    1991m7       199       235  
         Canada      UNTSO    1991m8       235       235  
         Canada      UNTSO    1991m9       203       235  
         Canada      UNTSO   1991m10       205       235  
         Canada      UNTSO   1991m11       161       235  
         Canada      UNTSO   1991m12       159       235

With that said, some advice I give to new users of Stata.

Stata's "date and time" variables are complicated and there is a lot to learn. If you have not already read the very detailed Chapter 24 (Working with dates and times) of the Stata User's Guide PDF, do so now. If you have, it's time for a refresher. After that, the help datetime documentation will usually be enough to point the way. You can't remember everything; even the most experienced users end up referring to the help datetime documentation or back to the manual for details. But at least you will get a good understanding of the basics and the underlying principles. An investment of time that will be amply repaid.

More broadly, I'm sympathetic to you as a new user of Stata - it's a lot to absorb. And even worse if perhaps you are under pressure to produce some output quickly. Nevertheless, I'd like to encourage you to take a step back from your immediate tasks.

When I began using Stata in a serious way, I started, as have others here, by reading my way through the Getting Started with Stata manual relevant to my setup. Chapter 18 then gives suggested further reading, much of which is in the Stata User's Guide, and I worked my way through much of that reading as well. There are a lot of examples to copy and paste into Stata's do-file editor to run yourself, and better yet, to experiment with changing the options to see how the results change.

All of these manuals are included as PDFs in the Stata installation (since version 11) and are accessible from within Stata - for example, through the PDF Documentation section of Stata's Help menu. The objective in doing the reading was not so much to master Stata as to be sure I'd become familiar with a wide variety of important basic techniques, so that when the time came that I needed them, I might recall their existence, if not the full syntax, and know how to find out more about them in the help files and PDF manuals.

Stata supplies exceptionally good documentation that amply repays the time spent studying it - there's just a lot of it. The path I followed surfaces the things you need to know to get started in a hurry and to work effectively.

[Josh Weiner]

Thank you for all your help, William! It worked quite well and is greatly appreciated. I appreciate the more general advice as well, and I'll give Chapter 24, Getting Started with Stata, and the Stata User's Guide a read. I've read a number of the help files to learn along the way, but, as you said, a more general overview to at least make me aware of some commands I might not have experience with would be helpful.
Thanks again!