Help creating a dummy column

[Parvesh Seeballack]

Hi guys!

I need help creating a new column in my dataset (provided below).

* Example generated by -dataex-. To install: ssc install dataex
clear
input long yearmo float(year mo bwi)
200401 2004  1 -.43
200402 2004  2 -.33
200403 2004  3 -.36
200404 2004  4 -.26
200405 2004  5 -.27
200406 2004  6 -.17
200407 2004  7  .09
200408 2004  8  .22
200409 2004  9  .14
200410 2004 10  .19
200411 2004 11  .21
200412 2004 12  .18
200501 2005  1  .24
200502 2005  2  .15
200503 2005  3  .07
200504 2005  4  .25
200505 2005  5  .24
200506 2005  6  .15
200507 2005  7  .05
200508 2005  8  .17
200509 2005  9  .35
200510 2005 10  .37
200511 2005 11  .27
200512 2005 12  .35
200601 2006  1  .35
200602 2006  2  .28
200603 2006  3  .23
200604 2006  4  .17
200605 2006  5  .36
200606 2006  6   .4
200607 2006  7  .47
200608 2006  8   .5
200609 2006  9  .33
200610 2006 10   .3
200611 2006 11  .48
200612 2006 12  .53
200701 2007  1  .48
200702 2007  2   .7
200703 2007  3  .84
200704 2007  4  .87
200705 2007  5  .62
200706 2007  6  .69
200707 2007  7  .61
200708 2007  8  .38
200709 2007  9  .34
200710 2007 10  .35
200711 2007 11  .42
200712 2007 12  .42
200801 2008  1  .41
200802 2008  2  .15
200803 2008  3  .08
200804 2008  4  .01
200805 2008  5 -.07
200806 2008  6 -.01
200807 2008  7  .01
200808 2008  8  .09
200809 2008  9 -.08
200810 2008 10 -.22
200811 2008 11 -.76
200812 2008 12 -.67
200901 2009  1 -.54
200902 2009  2  -.7
200903 2009  3 -.73
200904 2009  4 -.89
200905 2009  5 -.83
200906 2009  6 -.83
200907 2009  7 -.56
200908 2009  8 -.35
200909 2009  9 -.54
200910 2009 10 -.56
200911 2009 11 -.57
200912 2009 12 -.68
201001 2010  1  -.8
201002 2010  2  -.8
201003 2010  3 -.89
201004 2010  4 -.69
201005 2010  5 -.79
201006 2010  6 -.63
201007 2010  7 -.64
201008 2010  8 -.47
201009 2010  9  -.3
201010 2010 10 -.39
201011 2010 11 -.39
201012 2010 12 -.22
201101 2011  1 -.07
201102 2011  2  .04
201103 2011  3  .08
201104 2011  4  .15
201105 2011  5  .38
201106 2011  6  .32
201107 2011  7  .27
201108 2011  8  .29
201109 2011  9  .28
201110 2011 10  .17
201111 2011 11  .05
201112 2011 12  .01
201201 2012  1 -.13
201202 2012  2  -.1
201203 2012  3  .02
201204 2012  4 -.09
end

I want to create a column (high_sentiment) which takes a value of 1 for all months in that year if the previous end of year (December) value is greater than the full sample mean.

[Clyde Schechter]

This will do it:

//    FIRST GET THE OVERALL SAMPLE MEAN OF BWI
summ bwi, meanonly
local sample_mean `r(mean)'

//    CREATE A VARIABLE CONTAINING THE PREVIOUS END OF YEAR VALUE
sort year mo
rangestat (last) bwi, interval(year -1 -1)

//    CREATE THE WANTED VARIABLE
gen byte high_sentiment = (bwi_last > `sample_mean') if !missing(bwi_last)

Of course, for the first year of data, there is no previous end of year, so the high_sentiment variable is missing for those observations.

To run this code you need the -rangestat- command, written by Robert Picard, Nick Cox, and Roberto Ferrer. It is available from SSC.

As an aside, your yearmo variable will be useless for data management in Stata. For a variable that expresses both the year and month, you should create a Stata internal format monthly variable:

drop yearmo
gen mdate = ym(year, mo)
format mdate %tm

[Parvesh Seeballack]

Thanks Clyde.

Really appreciate your help.

I can now carry on with the rest of my analysis!