Creating Lag Values by month

[Nihar Singh]

Hi All,

I want to use the observations of the previous month (the value of each observation is the same for a particular month) for the current month for each co_code. But for each co_code, the number of observations each month is not the same.

Here is an example of my dataset:
[CODE]
* Example generated by -dataex-. To install: ssc install dataex
clear
input long co_code float(date2 cal_year month bse_avg_daily_firm_weight)
11 17811 2008 10 2.490623e-08
11 17812 2008 10 2.490623e-08
11 17813 2008 10 2.490623e-08
11 17815 2008 10 2.490623e-08
11 17818 2008 10 2.490623e-08
11 17819 2008 10 2.490623e-08
11 17820 2008 10 2.490623e-08
11 17821 2008 10 2.490623e-08
11 17822 2008 10 2.490623e-08
11 17825 2008 10 2.490623e-08
11 17826 2008 10 2.490623e-08
11 17827 2008 10 2.490623e-08
11 17828 2008 10 2.490623e-08
11 17829 2008 10 2.490623e-08
11 17832 2008 10 2.490623e-08
11 17833 2008 10 2.490623e-08
11 17834 2008 10 2.490623e-08
11 17836 2008 10 2.490623e-08
11 17839 2008 11 2.778235e-08
11 17840 2008 11 2.778235e-08
11 17841 2008 11 2.778235e-08
11 17842 2008 11 2.778235e-08
11 17843 2008 11 2.778235e-08
11 17846 2008 11 2.778235e-08
11 17847 2008 11 2.778235e-08
11 17848 2008 11 2.778235e-08
11 17850 2008 11 2.778235e-08
11 17853 2008 11 2.778235e-08
11 17854 2008 11 2.778235e-08
11 17855 2008 11 2.778235e-08
11 17856 2008 11 2.778235e-08
11 17857 2008 11 2.778235e-08
11 17860 2008 11 2.778235e-08
11 17861 2008 11 2.778235e-08
11 17862 2008 11 2.778235e-08
11 17864 2008 11 2.778235e-08

I want to use the bse_avg_daily_firm_weight of month 10th for each observation in month 11th and so on. But the number of observations each month is different

Kindly, help with the code

[Andrew Musau]

The values of the variable bse_avg_daily_firm_weight look too small to be held as floats. See

help precision

gen ym= ym(cal_year, month)
format ym %tm
bys co_code (date2): gen wanted= cond((ym!=ym[_n-1])& ym==ym[_n-1]+1, bse_avg_daily_firm_weight[_n-1], .)
bys ym (wanted): replace wanted= wanted[1]

[Nihar Singh]

Hi Andrew,

I tried running your code, but it is not working.

[Andrew Musau]

it is not working

does not provide sufficient information to push this forward. This detail is covered in the FAQ Advice. Provide a sample of your data using the dataex command, show the exact code that you ran and explain why the result differs from your expectation.

[Nihar Singh]

I ran this command-
gen ym= ym(cal_year, month) format ym %tm bys co_code (date2): gen wanted= cond((ym!=ym[_n-1])& ym==ym[_n-1]+1, bse_avg_daily_firm_weight[_n-1], .) bys ym (wanted): replace wanted= wanted[1] (as suggested by Andrew) The output after I ran the command-
input long co_code float(date2 cal_year month bse_avg_daily_firm_weight ym wanted)
11 17813 2008 10 2.490623e-08 585 2.0932713e-09
11 17827 2008 10 2.490623e-08 585 2.0932713e-09
11 17828 2008 10 2.490623e-08 585 2.0932713e-09
11 17834 2008 10 2.490623e-08 585 2.0932713e-09
11 17818 2008 10 2.490623e-08 585 2.0932713e-09
11 17812 2008 10 2.490623e-08 585 2.0932713e-09
11 17815 2008 10 2.490623e-08 585 2.0932713e-09
11 17819 2008 10 2.490623e-08 585 2.0932713e-09
11 17811 2008 10 2.490623e-08 585 2.0932713e-09
11 17833 2008 10 2.490623e-08 585 2.0932713e-09
11 17836 2008 10 2.490623e-08 585 2.0932713e-09
11 17832 2008 10 2.490623e-08 585 2.0932713e-09
11 17822 2008 10 2.490623e-08 585 2.0932713e-09
11 17825 2008 10 2.490623e-08 585 2.0932713e-09
11 17826 2008 10 2.490623e-08 585 2.0932713e-09
11 17829 2008 10 2.490623e-08 585 2.0932713e-09
11 17820 2008 10 2.490623e-08 585 2.0932713e-09
11 17821 2008 10 2.490623e-08 585 2.0932713e-09
11 17857 2008 11 2.778235e-08 586 2.1570497e-09
11 17839 2008 11 2.778235e-08 586 2.1570497e-09
11 17864 2008 11 2.778235e-08 586 2.1570497e-09
11 17843 2008 11 2.778235e-08 586 2.1570497e-09
11 17860 2008 11 2.778235e-08 586 2.1570497e-09
11 17854 2008 11 2.778235e-08 586 2.1570497e-09
11 17856 2008 11 2.778235e-08 586 2.1570497e-09
11 17846 2008 11 2.778235e-08 586 2.1570497e-09
11 17842 2008 11 2.778235e-08 586 2.1570497e-09
11 17862 2008 11 2.778235e-08 586 2.1570497e-09
11 17841 2008 11 2.778235e-08 586 2.1570497e-09
11 17855 2008 11 2.778235e-08 586 2.1570497e-09
11 17850 2008 11 2.778235e-08 586 2.1570497e-09
11 17847 2008 11 2.778235e-08 586 2.1570497e-09
11 17840 2008 11 2.778235e-08 586 2.1570497e-09
11 17848 2008 11 2.778235e-08 586 2.1570497e-09
11 17861 2008 11 2.778235e-08 586 2.1570497e-09
11 17853 2008 11 2.778235e-08 586 2.1570497e-09

The output which I want is:
input long co_code float(date2 cal_year month bse_avg_daily_firm_weight ym wanted)
11 17813 2008 10 2.490623e-08 585 2.0932713e-09
11 17827 2008 10 2.490623e-08 585 2.0932713e-09
11 17828 2008 10 2.490623e-08 585 2.0932713e-09
11 17834 2008 10 2.490623e-08 585 2.0932713e-09
11 17818 2008 10 2.490623e-08 585 2.0932713e-09
11 17812 2008 10 2.490623e-08 585 2.0932713e-09
11 17815 2008 10 2.490623e-08 585 2.0932713e-09
11 17819 2008 10 2.490623e-08 585 2.0932713e-09
11 17811 2008 10 2.490623e-08 585 2.0932713e-09
11 17833 2008 10 2.490623e-08 585 2.0932713e-09
11 17836 2008 10 2.490623e-08 585 2.0932713e-09
11 17832 2008 10 2.490623e-08 585 2.0932713e-09
11 17822 2008 10 2.490623e-08 585 2.0932713e-09
11 17825 2008 10 2.490623e-08 585 2.0932713e-09
11 17826 2008 10 2.490623e-08 585 2.0932713e-09
11 17829 2008 10 2.490623e-08 585 2.0932713e-09
11 17820 2008 10 2.490623e-08 585 2.0932713e-09
11 17821 2008 10 2.490623e-08 585 2.0932713e-09
11 17857 2008 11 2.778235e-08 586 2.490623e-08
11 17839 2008 11 2.778235e-08 586 2.490623e-08
11 17864 2008 11 2.778235e-08 586 2.490623e-08
11 17843 2008 11 2.778235e-08 586 2.490623e-08
11 17860 2008 11 2.778235e-08 586 2.490623e-08
11 17854 2008 11 2.778235e-08 586 2.490623e-08
11 17856 2008 11 2.778235e-08 586 2.490623e-08
11 17846 2008 11 2.778235e-08 586 2.490623e-08
11 17842 2008 11 2.778235e-08 586 2.490623e-08
11 17862 2008 11 2.778235e-08 586 2.490623e-08
11 17841 2008 11 2.778235e-08 586 2.490623e-08
11 17855 2008 11 2.778235e-08 586 2.490623e-08
11 17850 2008 11 2.778235e-08 586 2.490623e-08
11 17847 2008 11 2.778235e-08 586 2.490623e-08
11 17840 2008 11 2.778235e-08 586 2.490623e-08
11 17848 2008 11 2.778235e-08 586 2.490623e-08
11 17861 2008 11 2.778235e-08 586 2.490623e-08
11 17853 2008 11 2.778235e-08 586 2.490623e-08

[Andrew Musau]

If you have several levels of co_code, you should also group by co_code in the last line. Also note the comment on precision in #2.

gen ym= ym(cal_year, month)
format ym %tm
bys co_code (date2): gen wanted= cond((ym!=ym[_n-1])& ym==ym[_n-1]+1, bse_avg_daily_firm_weight[_n-1], .)
bys co_code ym (wanted): replace wanted= wanted[1]

[Nihar Singh]

Thankyou Andrew. Grouping of co_code worked. I got the observations that I wanted.

I went through the command on precision mentioned by you in #2. But, it is not much helpful as it talks about data type (float or double) and not about the format in which observations are displayed.

[Andrew Musau]

The storage type and the display format are two distinct elements. The variable "bse_avg_daily_firm_weight" has storage type float, but the numbers look rather small. Did you import the data from, e.g., Excel? If Stata chose this storage type for you, it may be fine. To change the display format:

format bse_avg_daily_firm_weight wanted %13.12f
list in 1/5

See

help format

Res.:

. list in 1/5

     +----------------------------------------------------------------------------+
     | co_code   date2   cal_year   month   bse_avg_dail~t    ym           wanted |
     |----------------------------------------------------------------------------|
  1. |      11   17813       2008      10   0.000000024906   585   0.000000002093 |
  2. |      11   17827       2008      10   0.000000024906   585   0.000000002093 |
  3. |      11   17828       2008      10   0.000000024906   585   0.000000002093 |
  4. |      11   17834       2008      10   0.000000024906   585   0.000000002093 |
  5. |      11   17818       2008      10   0.000000024906   585   0.000000002093 |
     +----------------------------------------------------------------------------+

.

[Nihar Singh]

No, I have not imported the file from excel. I have manually calculated the variable "bse_avg_daily_firm_weight". Stata, by default, saves all the observations as float unless otherwise stated.

Thank you. I have changed the format by using the help format code.

[Andrew Musau]

No, I have not imported the file from excel. I have manually calculated the variable "bse_avg_daily_firm_weight"

In that case, I strongly recommend that you go back and specify

gen double bse_avg_daily_firm_weight = ...

when creating the variable.

[Nihar Singh]

Okay, Andrew. I will do the required.
Thank you.