Collecting a range of observations

[Jane Teo]

Hi

I have several columns of variables (funds returns) under analysis, how do i collect the last 12 observations of each column. And also, the first 12 observation of each column.. note that all columns (funds returns) have different starting and ending date and hence the inconsistency.

Thanks much!

[Oded Mcdossi]

What do you mean by collect? is it the sum of the first or last 12 observations? if the answer is yes, use:

egen new_var=total(var) in 1/12 // for total value of the first 12 observations

otherwise, please follow the FAQ for better chance to get useful answers.

[William Lisowski]

As Oded pointed out, it is not clear what you mean by collect. The following code shows how to identify the first and last observations (I chose 3 observations rather than 12 for my little example), and calculates totals of those. It should provide a starting point for accomplishing your actual objective.

. list, clean

            date   x1   x2  
  1.   01aug2015    .    .  
  2.   02aug2015    .    .  
  3.   03aug2015    .    .  
  4.   04aug2015    .    .  
  5.   05aug2015   30    .  
  6.   06aug2015   36    .  
  7.   07aug2015   42    .  
  8.   08aug2015    1    .  
  9.   09aug2015    7    .  
 10.   10aug2015   13   46  
 11.   11aug2015   19   40  
 12.   12aug2015   25   34  
 13.   13aug2015   31   28  
 14.   14aug2015   37   22  
 15.   15aug2015   43   16  
 16.   16aug2015    2   10  
 17.   17aug2015    8    4  
 18.   18aug2015   14   51  
 19.   19aug2015   20   45  
 20.   20aug2015   26   39  
 21.   21aug2015   32   33  
 22.   22aug2015   38   27  
 23.   23aug2015   44   21  
 24.   24aug2015    .   15  
 25.   25aug2015    .    9  
 26.   26aug2015    .    3  
 27.   27aug2015    .   50  
 28.   28aug2015    .   44  
 29.   29aug2015    .   38  
 30.   30aug2015    .   32  
 31.   31aug2015    .   26  

. generate f1 = sum(1) if !missing(x1)
(12 missing values generated)

. generate f2 = sum(1) if !missing(x2)
(9 missing values generated)

. gsort -date

. generate l1 = sum(1) if !missing(x1)
(12 missing values generated)

. generate l2 = sum(1) if !missing(x2)
(9 missing values generated)

. gsort date

. egen y1 = total(x1) if f1<=3 | l1<=3
(25 missing values generated)

. egen y2 = total(x2) if f2<=3 | l2<=3
(25 missing values generated)

. list, clean

            date   x1   x2   f1   f2   l1   l2    y1    y2  
  1.   01aug2015    .    .    .    .    .    .     .     .  
  2.   02aug2015    .    .    .    .    .    .     .     .  
  3.   03aug2015    .    .    .    .    .    .     .     .  
  4.   04aug2015    .    .    .    .    .    .     .     .  
  5.   05aug2015   30    .    1    .   19    .   222     .  
  6.   06aug2015   36    .    2    .   18    .   222     .  
  7.   07aug2015   42    .    3    .   17    .   222     .  
  8.   08aug2015    1    .    4    .   16    .     .     .  
  9.   09aug2015    7    .    5    .   15    .     .     .  
 10.   10aug2015   13   46    6    1   14   22     .   216  
 11.   11aug2015   19   40    7    2   13   21     .   216  
 12.   12aug2015   25   34    8    3   12   20     .   216  
 13.   13aug2015   31   28    9    4   11   19     .     .  
 14.   14aug2015   37   22   10    5   10   18     .     .  
 15.   15aug2015   43   16   11    6    9   17     .     .  
 16.   16aug2015    2   10   12    7    8   16     .     .  
 17.   17aug2015    8    4   13    8    7   15     .     .  
 18.   18aug2015   14   51   14    9    6   14     .     .  
 19.   19aug2015   20   45   15   10    5   13     .     .  
 20.   20aug2015   26   39   16   11    4   12     .     .  
 21.   21aug2015   32   33   17   12    3   11   222     .  
 22.   22aug2015   38   27   18   13    2   10   222     .  
 23.   23aug2015   44   21   19   14    1    9   222     .  
 24.   24aug2015    .   15    .   15    .    8     .     .  
 25.   25aug2015    .    9    .   16    .    7     .     .  
 26.   26aug2015    .    3    .   17    .    6     .     .  
 27.   27aug2015    .   50    .   18    .    5     .     .  
 28.   28aug2015    .   44    .   19    .    4     .     .  
 29.   29aug2015    .   38    .   20    .    3     .   216  
 30.   30aug2015    .   32    .   21    .    2     .   216  
 31.   31aug2015    .   26    .   22    .    1     .   216

With that said, please review the Statalist FAQ linked to from the top of the page, especially sections 9-12 on how to best pose your question. It's particularly helpful to copy commands and output from your Stata Results window and paste them into your Statalist post using CODE delimiters, as described in section 12 of the FAQ.