calculate mean for a time interval for cross sectional data

[Nan Liu]

Hi, I am relatively new to STATA. I have cross sectional data on property transactions over a long period of time. I'd like to generate a new variable, that is the mean (and other measures) of nearby prices of recently sold properties.....so basically, for each property, I have a listing date (the date the property is put on the market), I would like to work out the average transaction prices (on the sold date) of nearby properties in the same neighbourhood (I have a variable on this) that were sold within three months of that listing date.....

I found this thread: https://www.statalist.org/forums/for...-a-time-period

Which is similar to what I wanted to do, but in my case,for each observation, the number of nearby recent transactions can be any number.....
hope someone could help. Thank you.

[Clyde Schechter]

As you don't provide example data, I can't show you code, because the code would depend on details of your data. But I can say that the simplest solution to your problem will come from using the -rangestat- command, written by Robert Picard, Nick Cox, and Roberto Ferrer. You can get it from SSC. Read -help rangestat- after you install it for instructions and examples.

In the future, when asking for help with code, show example data, and please use the -dataex- command to do so. If you are running version 15.1 or a fully updated version 14.2, it is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.



When asking for help with code, always show example data. When showing example data, always use -dataex-.

[Nan Liu]

Thank you Clyde,
please see the sample data below. F

or each id, I have

a listdate (

the date the property is put on the market), I would like to work out the average prices (on the sold date

) of nearby properties in the same neighbourhood that were sold within three months of that list date.

clear
input long id double price int(listdate date) str4 neighbourhood
150500   59950 12913 14206 "AB32"
151083   32750 12933 14908 "AB24"
157970  161000 13286 14614 "AB24"
159184   32500 13332 16166 "AB24"
159647   33000 13354 15576 "AB24"
165768   40000 13669 14412 "AB24"
166714   34500 13710 14168 "AB24"
167206   46500 13734 14087 "AB24"
167253   37000 13737 14784 "AB24"
168710   66500 13809 14780 "AB32"
169209   25000 13846 15557 "AB24"
169513   37500 13891 14075 "AB24"
169527   61000 13891 14111 "AB32"
169495   68500 13891 14119 "AB32"
169595   59750 13895 14154 "AB32"
169614   47000 13898 14083 "AB24"
169669   72500 13900 14034 "AB32"
169726   50500 13902 14047 "AB32"
169778   75000 13905 14034 "AB32"
169748   31500 13905 14299 "AB24"
169840   74500 13908 14115 "AB32"
169942   74000 13913 14061 "AB32"
169977   23011 13914 14140 "AB24"
170035   33000 13919 14080 "AB24"
170065   43000 13919 14104 "AB32"
170103   39000 13920 14251 "AB24"
170113   38000 13921 14047 "AB24"
170147   51000 13921 14129 "AB32"
170185   48000 13923 14118 "AB32"
170279   30500 13927 14459 "AB24"
170324  149500 13928 14080 "AB32"
170306   29000 13928 14188 "AB24"
170328   24000 13928 14360 "AB24"
170364   44500 13930 14068 "AB32"
170350   67500 13930 14486 "AB32"
170379   37500 13933 14097 "AB24"
170437   49995 13933 14224 "AB32"
170371   44100 13933 14278 "AB24"
170381   43000 13933 14626 "AB32"
170515   52000 13936 14049 "AB24"
170524   68000 13937 14042 "AB32"
170539   53000 13937 14117 "AB32"
170577   33500 13940 14374 "AB24"
170618   45000 13940 14705 "AB32"
170647   28000 13941 14048 "AB24"
170700   67000 13943 14091 "AB32"
170687   49000 13943 14098 "AB24"
170701 7424683 13943 15027 "AB24"
170718   39500 13944 14123 "AB24"
170734   55750 13947 14033 "AB24"
170769   31000 13947 14038 "AB24"
170753  163000 13947 14059 "AB32"
170733  180000 13947 14096 "AB32"
170783   41000 13948 14327 "AB24"
170823   56006 13950 14146 "AB32"
170863   73500 13951 14045 "AB32"
170836   29500 13951 14063 "AB24"
170927   83000 13954 14066 "AB32"
170896   34156 13954 14213 "AB24"
170864   40000 13954 14273 "AB24"
170869   22500 13954 14826 "AB24"
170953   49000 13956 14041 "AB32"
170987   38000 13957 14042 "AB24"
171016   58500 13961 14049 "AB32"
171111   71000 13962 14082 "AB24"
171116   37500 13962 14102 "AB24"
171112   39111 13962 14276 "AB24"
171133   33000 13963 14147 "AB24"
171161   53500 13964 14068 "AB24"
171165   27250 13964 14220 "AB24"
171153   34000 13964 14298 "AB24"
171175   97500 13965 14066 "AB32"
171181   50000 13965 14192 "AB32"
171204   74000 13968 14075 "AB32"
171228   50000 13968 14091 "AB32"
171237   57000 13968 14192 "AB24"
171243   68500 13968 14335 "AB24"
171249   39000 13968 14717 "AB24"
171298   62000 13969 14098 "AB32"
171327  101000 13970 14045 "AB24"
171308   28000 13970 14381 "AB24"
171416   59000 13975 14132 "AB32"
171481   32000 13977 14397 "AB24"
171585   46500 13982 14082 "AB24"
171570   59529 13982 14110 "AB32"
171549   53500 13982 14202 "AB32"
171593   69096 13983 14038 "AB32"
171634   60000 13985 14108 "AB24"
171665   77117 13986 14075 "AB32"
171667   39500 13986 14292 "AB24"
171673   47500 13986 14362 "AB24"
171653   32000 13986 14614 "AB24"
171725   61000 13990 14040 "AB32"
171707   30000 13990 14269 "AB24"
171746   55250 13991 14052 "AB24"
171806   56500 13993 14080 "AB24"
171791   36500 13993 14199 "AB24"
171786   25500 13993 14311 "AB24"
171868   44500 13996 14153 "AB32"
171902   66500 13996 14412 "AB24"
end
format %tdnn/dd/CCYY listdate
format %tdnn/dd/CCYY date

[Clyde Schechter]

OK, the phrase "within three months" is ambiguous in a number of ways. Below I show code for three approaches, each based on a different meaning. The first is based on the assumption that "within three months" means during the period from the date of listing itself to 90 days later. The second is based on the assumption that "within three months" means during the period that covers the entire calendar month of the listing date and continues through the third calendar month after that. (E.g. if the sale date is 20 September 2018, this would go from 1 September 2018 through 30 November 2018.) The third assumes that the period extending from 45 days before the list date to 45 days after is intended.

gen high1 = listdate + 90
rangestat (mean) mean_price_1 = price, by(neighbourhood) interval(date listdate high1)

gen mdate = mofd(date)
gen mlistdate = mofd(listdate)
format mdate mlistdate %tm
gen high2 = mlistdate + 2
rangestat (mean) mean_price_2 = price, by(neighbourhood) interval(mdate mlistdate high2)

gen high3 = listdate + 45
gen low3 = listdate - 45
format low3 high3 %td
rangestat (mean) mean_price_3 = price, by(neighbourhood) interval(date low3 high3)

There are still other ways that "within three months" might be interpreted. I will leave it to you to modify the code, if necessary, based on these examples, to correspond to whatever meaning you had in mind.