Assigning Values of Variable for All Panels

[Maxence Morlet]

Hi all,

I have tried finding a post already discussing this issue but have not found any; apologies in advance if one exists

Here is my data:
input float(SecId1 dm) double WeeklyReturn float tb3ms
4 705 .5516592499999999 2.25
4 706 .606598 2.33
4 707 -.4112093999999999 2.37
4 708 1.64722825 2.37
4 709 .33371999999999996 2.39
4 710 1.1331326000000002 2.4
4 711 .09498449999999997 2.38
4 712 -1.9586029999999999 2.35
4 713 1.67149 2.17
4 714 -1.3865355 2.1
4 715 -.34597675000000006 1.95
4 716 .21807700000000008 1.89
4 717 1.6438857500000001 1.65
4 718 -.02667074999999998 1.54
4 719 1.3116976 1.54
4 720 -1.0276205 1.52
4 721 -.9711949999999999 1.52
4 722 -3.6885015999999995 .29
4 723 2.11304575 .14
4 724 2.6366712 .13
4 725 .945435 .16
4 726 1.3224075 .13
4 727 .5792539999999999 .1
4 728 -.39374025 .11
4 729 .061644750000000026 .1
4 730 2.6141734000000003 .09
4 731 .96156325 .09
4 732 1.7286028000000002 .08
4 733 -.7715675000000002 .04
4 734 -.0966455 .03
4 735 .10510433333333336 .02
5 705 -.95295275 .
5 706 -.019057249999999915 .
5 707 -1.358661 .
5 708 1.3026425 .
5 709 .6011622499999999 .
5 710 .17128120000000008 .
5 711 -.054412 .
5 712 -2.04645475 .
5 713 1.3629257999999997 .
5 714 -.81613775 .
5 715 -.5691437500000001 .
5 716 .5598118000000001 .
5 717 1.3654247499999999 .
5 718 .3868075 .
5 719 .4214298 .
5 720 -.84145575 .
5 721 -1.54410075 .
5 722 -2.7440836 .
5 723 1.583352 .
5 724 1.9272106 .
5 725 -.7555624999999999 .
5 726 .09120900000000004 .
5 727 .8842686000000001 .
5 728 -.53119275 .
5 729 -.513367 .
5 730 2.970973 .
5 731 .19980999999999996 .
5 732 .5054336 .
5 733 .5529535 .
5 734 .6751975 .
5 735 .5922396666666666 .



SecId1 is the panel variable and dm the monthly variable.

tb3ms is the three month Treasury bill rate that I downloaded separately from the dataset and then pasted onto it. I would like to compute the excess return of each security i.e. WeeklyReturn - tb3ms.

For this, I would need to assign the same 31 values for tb3ms (one for each month) to each panel. How could this be achieved? I have tried using expand, which failed. I also tried using David Kantor's "carryforward" command, combined with tsfill, but this also failed.

I would greatly appreciate any help!

Best regards,
Maxence

[William Lisowski]

This example should start you in a useful direction.

// just 5 observations for the example
// Example generated by -dataex-. For more info, type help dataex
clear
input float(SecId1 dm) double WeeklyReturn float tb3ms
4 705    .5516592499999999 2.25
4 706              .606598 2.33
4 707   -.4112093999999999 2.37
4 708           1.64722825 2.37
4 709   .33371999999999996 2.39
5 705           -.95295275    .
5 706 -.019057249999999915    .
5 707            -1.358661    .
5 708            1.3026425    .
5 709    .6011622499999999    .
end
format %tm dm

// confirm that tb3ms is present only and always when SecId1 is 4
assert (tb3ms!=.) == (SecId1==4)
sort dm SecId1
// assert that the first SecId1 is always 4
by dm: assert SecId1==4 if _n==1
by dm: replace tb3ms = tb3ms[1] if _n>1
sort SecId1 dm
list, sepby(SecId1) abbreviate(12)

. // just 5 observations for the example
. // Example generated by -dataex-. For more info, type help dataex
. clear

. input float(SecId1 dm) double WeeklyReturn float tb3ms

        SecId1         dm  WeeklyRe~n      tb3ms
  1. 4 705    .5516592499999999 2.25
  2. 4 706              .606598 2.33
  3. 4 707   -.4112093999999999 2.37
  4. 4 708           1.64722825 2.37
  5. 4 709   .33371999999999996 2.39
  6. 5 705           -.95295275    .
  7. 5 706 -.019057249999999915    .
  8. 5 707            -1.358661    .
  9. 5 708            1.3026425    .
 10. 5 709    .6011622499999999    .
 11. end

. format %tm dm

. 
. // confirm that tb3ms is present only and always when SecId1 is 4
. assert (tb3ms!=.) == (SecId1==4)

. sort dm SecId1

. // assert that the first SecId1 is always 4
. by dm: assert SecId1==4 if _n==1

. by dm: replace tb3ms = tb3ms[1] if _n>1
(5 real changes made)

. sort SecId1 dm

. list, sepby(SecId1) abbreviate(12)

     +-----------------------------------------+
     | SecId1        dm   WeeklyReturn   tb3ms |
     |-----------------------------------------|
  1. |      4   2018m10      .55165925    2.25 |
  2. |      4   2018m11        .606598    2.33 |
  3. |      4   2018m12      -.4112094    2.37 |
  4. |      4    2019m1      1.6472282    2.37 |
  5. |      4    2019m2         .33372    2.39 |
     |-----------------------------------------|
  6. |      5   2018m10     -.95295275    2.25 |
  7. |      5   2018m11     -.01905725    2.33 |
  8. |      5   2018m12      -1.358661    2.37 |
  9. |      5    2019m1      1.3026425    2.37 |
 10. |      5    2019m2      .60116225    2.39 |
     +-----------------------------------------+

[Maxence Morlet]

Brilliant, works perfectly. Thank you!