Announcement

Collapse
No announcement yet.
X
Collapse
  • Page of 1
  • Filter
  • Time
  • Show
Clear All
new posts
  • #1

    Expanding quarters

    Dear all,

    I have the following quarterly dataset where is based on data of 2017q4

    Code:
    * Example generated by -dataex-. To install: ssc install dataex
clear
input str12 isin float(minimum_tick2017 mean_tick2017 meadian_tick2017 calendar_qtr)
"ANN4327C1220"   .01         .275  .115 231
"AT000000STR1"   .01    .08549383   .04 231
"AT00000VIE62"   .01     .0681383   .04 231
"AT0000609607"   .01    .04763533   .03 231
"AT0000616701"     1            1     1 231
"AT0000641352"   .01    .02512557   .02 231
"AT0000644505"    .1    .15876034    .1 231
"AT0000652250"   .01    .02949597   .02 231
"AT0000690151"   .22          .22   .22 231
"AT0000720008"  .001   .009914697  .007 231
"AT0000723606"     1            1     1 231
"AT0000728209"   3.8          3.8   3.8 231
"AT0000730007"   .01   .036048338   .03 231
"AT0000743059"   .01    .04165638   .03 231
"AT0000758032"   7.2          7.2   7.2 231
"AT0000758305"   .01    .09023758   .06 231
"AT0000762406"  4.39         4.39  4.39 231
"AT0000767306"     .            .     . 231
"AT0000785407"   .01     .0573913   .04 231
"AT0000785555"   .01    .07048387   .04 231
"AT0000797303"     1            1     1 231
"AT0000808209"    .5           .5    .5 231
"AT0000815402"   .01    .12735294  .065 231
"AT0000827209"  .001  .0084385965  .005 231
"AT0000831706"   .01   .024517374   .02 231
"AT0000834007"   .01    .10222222   .07 231
"AT0000837307"  .001   .015046064   .01 231
"AT0000922554"    .1    .23555556    .2 231
"AT0000937503"   .01   .034719724   .03 231
"AT0000938204"    .1     .2772532    .2 231
"AT0000946652"   .01    .10599832   .07 231
"AT0000969985"   .01    .05120102  .035 231
"AT0000A00XX9"   .01    .04177291   .03 231
"AT0000A00Y78"  .001       .01825   .01 231
"AT0000A0E9W5"   .01   .031373456   .02 231
"AT0000A0Z9G3"     .            .     . 231
"AT0000A18XM4" .0337    .08505532 .0676 231
"AT0000A1PY49"    .8           .8    .8 231
"AT0000APOST4"   .01   .036590084   .03 231
"AT000KAPSCH9"   .01     .1181188   .07 231
"AU0000057408"   .01       .01625   .01 231
"BE0003215143"  2.24         2.24  2.24 231
"BE0003463685"     .            .     . 231
"BE0003470755"   .05    .06404126   .05 231
"BE0003545531"    .1           .4    .2 231
"BE0003555639"   .01   .029804984   .02 231
"BE0003573814"    .1     .6166667    .4 231
"BE0003575835"  7.97         7.97  7.97 231
"BE0003593044"    .1     .1129386    .1 231
"BE0003599108"    .1     .2880597    .2 231
"BE0003602134"     4            4     4 231
"BE0003604155"     1     4.417808   3.5 231
"BE0003654655"   .03          .03   .03 231
"BE0003656676"   .01    .02110465   .02 231
"BE0003678894"   .01     .0286398   .02 231
"BE0003696102" .0001 .00024666666 .0001 231
"BE0003707214"    .1    .35833335    .2 231
"BE0003720340"   .01    .07366595   .05 231
"BE0003724383"   .01       .17624   .09 231
"BE0003731453"   2.9          2.9   2.9 231
"BE0003734481"    .3           .3    .3 231
"BE0003735496"   .01    .02064815   .01 231
"BE0003739530"   .01    .03056279   .02 231
"BE0003741551"   .01    .05557377   .02 231
"BE0003743573"   .01    .07663935   .04 231
"BE0003746600"   .01     .0476506   .03 231
"BE0003748622"   .01         .071   .05 231
"BE0003752665"   .11          .11   .11 231
"BE0003754687"   .01     .0876087   .04 231
"BE0003755692"  .001   .004564706  .003 231
"BE0003760742"    .1     .4756757    .2 231
"BE0003764785"   .05     .0948052   .05 231
"BE0003765790"  .005    .03016129   .02 231
"BE0003766806"   .01   .035563283   .03 231
"BE0003770840"   .01    .26102564  .155 231
"BE0003773877"   .01          .01   .01 231
"BE0003789063"  .001    .00807173  .005 231
"BE0003797140"   .01   .023444476   .02 231
"BE0003798155"   2.8          2.8   2.8 231
"BE0003807246"    .1     .4142857    .3 231
"BE0003808251"  .001    .01322477 .0075 231
"BE0003810273"  .005   .011289797  .005 231
"BE0003816338"  .005    .03732143  .025 231
"BE0003818359"   .01    .04378951   .03 231
"BE0003820371"   .01     .0456903   .03 231
"BE0003823409"   .01    .06314286   .04 231
"BE0003825420"    .3           .3    .3 231
"BE0003826436"   .01    .02313203   .02 231
"BE0003836534"  .001  .0017118645  .001 231
"BE0003839561"   .01    .08870967   .05 231
"BE0003844611"  .001   .010302425  .006 231
"BE0003846632"  .001    .01290818  .007 231
"BE0003851681"   .01    .05445932   .04 231
"BE0003853703"   .01    .10534965   .08 231
"BE0003856730"   .01    .11621428  .045 231
"BE0003858751"   .01    .13682692   .06 231
"BE0003870871"   .01   .018333333   .01 231
"BE0003874915"  .005   .012671382   .01 231
"BE0003878957"    .1    .18527132    .1 231
"BE0003880979"  .001   .014068182  .007 231
end
format %tq calendar_qtr
    I am trying to expand all the values in the variables, by creating 2017q1 2017q2 and 2017q3.

    I know is possible to do by using -expand-. Nevertheless, struggling to implement it

    Do you have any advice? Many thanks
    Tags: None
  • #2
    We can't see what you tried to comment on where you're stuck.

    You can always go

    Code:
    expand 4  

bysort calendar_qtr  : replace calendar_qtr = calendar_qtr - _n + 1
    What do you gain thereby?
    Last edited by Nick Cox; 10 Jan 2022, 06:18.

    Comment

    • #3
      Dear Nick Cox
      Thanks for your assistance.

      I implemented the code that you suggested but I have an issue in the calendar_qtr variable. This is my time variable that it should be from q1 to q4 of year 2017. (Before to expand I only have q4).

      Code:
      * Example generated by -dataex-. To install: ssc install dataex
clear
input str12 isin float(minimum_tick2017 mean_tick2017 median_tick2017 calendar_qtr)
"ANN4327C1220"  .01        .275 .115 -3962
"ANN4327C1220"  .01        .275 .115 -3961
"ANN4327C1220"  .01        .275 .115 -3960
"ANN4327C1220"  .01        .275 .115   231
"AT000000STR1"  .01   .08549383  .04 -3965
"AT000000STR1"  .01   .08549383  .04 -3964
"AT000000STR1"  .01   .08549383  .04 -3963
"AT000000STR1"  .01   .08549383  .04   230
"AT00000VIE62"  .01    .0681383  .04 -3968
"AT00000VIE62"  .01    .0681383  .04 -3967
"AT00000VIE62"  .01    .0681383  .04 -3966
"AT00000VIE62"  .01    .0681383  .04   229
"AT0000609607"  .01   .04763533  .03 -3971
"AT0000609607"  .01   .04763533  .03 -3970
"AT0000609607"  .01   .04763533  .03 -3969
"AT0000609607"  .01   .04763533  .03   228
"AT0000616701"    1           1    1 -3974
"AT0000616701"    1           1    1 -3973
"AT0000616701"    1           1    1 -3972
"AT0000616701"    1           1    1   227
"AT0000641352"  .01   .02512557  .02 -3977
"AT0000641352"  .01   .02512557  .02 -3976
"AT0000641352"  .01   .02512557  .02 -3975
"AT0000641352"  .01   .02512557  .02   226
"AT0000644505"   .1   .15876034   .1 -3980
"AT0000644505"   .1   .15876034   .1 -3979
"AT0000644505"   .1   .15876034   .1 -3978
"AT0000644505"   .1   .15876034   .1   225
"AT0000652250"  .01   .02949597  .02 -3983
"AT0000652250"  .01   .02949597  .02 -3982
"AT0000652250"  .01   .02949597  .02 -3981
"AT0000652250"  .01   .02949597  .02   224
"AT0000690151"  .22         .22  .22 -3986
"AT0000690151"  .22         .22  .22 -3985
"AT0000690151"  .22         .22  .22 -3984
"AT0000690151"  .22         .22  .22   223
"AT0000720008" .001  .009914697 .007 -3989
"AT0000720008" .001  .009914697 .007 -3988
"AT0000720008" .001  .009914697 .007 -3987
"AT0000720008" .001  .009914697 .007   222
"AT0000723606"    1           1    1 -3992
"AT0000723606"    1           1    1 -3991
"AT0000723606"    1           1    1 -3990
"AT0000723606"    1           1    1   221
"AT0000728209"  3.8         3.8  3.8 -3995
"AT0000728209"  3.8         3.8  3.8 -3994
"AT0000728209"  3.8         3.8  3.8 -3993
"AT0000728209"  3.8         3.8  3.8   220
"AT0000730007"  .01  .036048338  .03 -3998
"AT0000730007"  .01  .036048338  .03 -3997
"AT0000730007"  .01  .036048338  .03 -3996
"AT0000730007"  .01  .036048338  .03   219
"AT0000743059"  .01   .04165638  .03 -4001
"AT0000743059"  .01   .04165638  .03 -4000
"AT0000743059"  .01   .04165638  .03 -3999
"AT0000743059"  .01   .04165638  .03   218
"AT0000758032"  7.2         7.2  7.2 -4004
"AT0000758032"  7.2         7.2  7.2 -4003
"AT0000758032"  7.2         7.2  7.2 -4002
"AT0000758032"  7.2         7.2  7.2   217
"AT0000758305"  .01   .09023758  .06 -4007
"AT0000758305"  .01   .09023758  .06 -4006
"AT0000758305"  .01   .09023758  .06 -4005
"AT0000758305"  .01   .09023758  .06   216
"AT0000762406" 4.39        4.39 4.39 -4010
"AT0000762406" 4.39        4.39 4.39 -4009
"AT0000762406" 4.39        4.39 4.39 -4008
"AT0000762406" 4.39        4.39 4.39   215
"AT0000767306"    .           .    . -4013
"AT0000767306"    .           .    . -4012
"AT0000767306"    .           .    . -4011
"AT0000767306"    .           .    .   214
"AT0000785407"  .01    .0573913  .04 -4016
"AT0000785407"  .01    .0573913  .04 -4015
"AT0000785407"  .01    .0573913  .04 -4014
"AT0000785407"  .01    .0573913  .04   213
"AT0000785555"  .01   .07048387  .04 -4019
"AT0000785555"  .01   .07048387  .04 -4018
"AT0000785555"  .01   .07048387  .04 -4017
"AT0000785555"  .01   .07048387  .04   212
"AT0000797303"    1           1    1 -4022
"AT0000797303"    1           1    1 -4021
"AT0000797303"    1           1    1 -4020
"AT0000797303"    1           1    1   211
"AT0000808209"   .5          .5   .5 -4025
"AT0000808209"   .5          .5   .5 -4024
"AT0000808209"   .5          .5   .5 -4023
"AT0000808209"   .5          .5   .5   210
"AT0000815402"  .01   .12735294 .065 -4028
"AT0000815402"  .01   .12735294 .065 -4027
"AT0000815402"  .01   .12735294 .065 -4026
"AT0000815402"  .01   .12735294 .065   209
"AT0000827209" .001 .0084385965 .005 -4031
"AT0000827209" .001 .0084385965 .005 -4030
"AT0000827209" .001 .0084385965 .005 -4029
"AT0000827209" .001 .0084385965 .005   208
"AT0000831706"  .01  .024517374  .02 -4034
"AT0000831706"  .01  .024517374  .02 -4033
"AT0000831706"  .01  .024517374  .02 -4032
"AT0000831706"  .01  .024517374  .02   207
end
format %tq calendar_qtr
      Don't know what is the driver of having this variable not properly coded/formatted.
      Thanks again for your time, really appreciated!

      Comment

      • #4
        Sorry; you need something quite different.

        Code:
        clear
input str12 isin float(minimum_tick2017 mean_tick2017 meadian_tick2017 calendar_qtr)
"ANN4327C1220"   .01         .275  .115 231
end
format calendar_qtr %tq

expand 4
bysort isin: replace calendar_qtr = calendar_qtr - _n + 1

list

    +----------------------------------------------------------+
     |         isin   min~2017   mean_t~7   meadia~7   calend~r |
     |----------------------------------------------------------|
  1. | ANN4327C1220        .01       .275       .115     2017q4 |
  2. | ANN4327C1220        .01       .275       .115     2017q3 |
  3. | ANN4327C1220        .01       .275       .115     2017q2 |
  4. | ANN4327C1220        .01       .275       .115     2017q1 |
     +----------------------------------------------------------+
        Last edited by Nick Cox; 10 Jan 2022, 08:50.

        Comment

        • #5
          Thanks Nick Cox . Really helpful!

          Comment

          Previous Next
          Working...
          X