Announcement

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

    Need help with multiply two particular rows of the data set that are conditional on some variables.

    My data is as below:

    Code:
    * Example generated by -dataex-. For more info, type help dataex
clear
input str8 date str9 cusip str6 ticker float(mktvalue r3000_wt r1000_wt r2000_wt) double(dt mn yr) byte(r1000 r2000)
"20010531" "216485102" "CSAV"   1000.96        0        . 1.00e-06 15126 5 2001 0 1
"20010531" "538146101" "LPSN"    1903.2        0        . 2.00e-06 15126 5 2001 0 1
"20010531" "68400F109" "OPUS"    2160.5        0        . 2.00e-06 15126 5 2001 0 1
"20010531" "68617E101" "OGNC"   2555.12        0        . 3.00e-06 15126 5 2001 0 1
"20010531" "29426X102" "EDG"    2974.16        0        . 3.00e-06 15126 5 2001 0 1
"20010531" "294352109" "EPRS"    3232.8        0        . 4.00e-06 15126 5 2001 0 1
"20010531" "879202109" "TLXS"   3265.08        0        . 4.00e-06 15126 5 2001 0 1
"20010531" "64115K103" "NPLI"   3704.54        0        . 4.00e-06 15126 5 2001 0 1
"20010531" "12476Q102" "CAIS"   4340.82        0        . 5.00e-06 15126 5 2001 0 1
"20010531" "92335X100" "VRDO"   4408.36        0        . 5.00e-06 15126 5 2001 0 1
"20010531" "124269101" "BUYX"    4503.6        0        . 5.00e-06 15126 5 2001 0 1
"20010531" "232743104" "CYCO"   4856.04        0        . 5.00e-06 15126 5 2001 0 1
"20010531" "106372105" "BWAYE"  5130.06        0        . 6.00e-06 15126 5 2001 0 1
"20010531" "90334D109" "AGA"    5146.96        0        . 6.00e-06 15126 5 2001 0 1
"20010531" "925298101" "VATA"   5223.96        0        . 6.00e-06 15126 5 2001 0 1
"20010531" "37243R208" "GNSL"   5238.09        0        . 6.00e-06 15126 5 2001 0 1
"20010531" "64107E107" "NTGX"    5509.2        0        . 6.00e-06 15126 5 2001 0 1
"20010531" "129897104" "CLIC"   5634.75        0        . 6.00e-06 15126 5 2001 0 1
"20010531" "88033A103" "TENF"   5821.34        0        . 7.00e-06 15126 5 2001 0 1
"20010531" "64114L102" "NETO"   6050.48        0        . 7.00e-06 15126 5 2001 0 1
"20010531" "64122T105" "NWKC"   6211.52 1.00e-06        . 7.00e-06 15126 5 2001 0 1
"20010531" "053566105" "AVEA"    6277.2 1.00e-06        . 7.00e-06 15126 5 2001 0 1
"20010531" "545754103" "LOUD"   6412.91 1.00e-06        . 7.00e-06 15126 5 2001 0 1
"20010531" "03828R104" "ATHY"    7004.7 1.00e-06        . 8.00e-06 15126 5 2001 0 1
"20010531" "978149102" "WOMN"   7376.22 1.00e-06        . 8.00e-06 15126 5 2001 0 1
"20010531" "92553R108" "VIAD"    7517.9 1.00e-06        . 8.00e-06 15126 5 2001 0 1
"20010531" "715329108" "PRSW"   7965.36 1.00e-06        . 9.00e-06 15126 5 2001 0 1
"20010531" "68338T106" "ONVI"    8097.3 1.00e-06        . 9.00e-06 15126 5 2001 0 1
"20010531" "37937R209" "GLBN"   8175.42 1.00e-06        . 9.00e-06 15126 5 2001 0 1
"20010531" "12500B105" "CBIS"   8273.43 1.00e-06        . 9.00e-06 15126 5 2001 0 1
"20010531" "24783N102" "DDDC"   8805.55 1.00e-06        . 1.00e-05 15126 5 2001 0 1
"20010531" "318224102" "FIRE"   9012.99 1.00e-06        . 1.00e-05 15126 5 2001 0 1
"20010531" "45073P101" "IBEM"   9046.11 1.00e-06        . 1.00e-05 15126 5 2001 0 1
"20010531" "262504103" "DSET"   9156.42 1.00e-06        . 1.00e-05 15126 5 2001 0 1
"20010531" "64120S109" "NASC"      9169 1.00e-06        . 1.00e-05 15126 5 2001 0 1
"20010531" "12877Q107" "CALD"   9389.38 1.00e-06        .  .000011 15126 5 2001 0 1
"20010531" "808760102" "SCIL"   9536.32 1.00e-06        .  .000011 15126 5 2001 0 1
"20010531" "87959Y103" "TGNQE" 10403.68 1.00e-06        .  .000012 15126 5 2001 0 1
"20010531" "755236106" "RAZF"  10443.26 1.00e-06        .  .000012 15126 5 2001 0 1
"20010531" "23437N104" "DALN"  10517.03 1.00e-06        .  .000012 15126 5 2001 0 1
"20010531" "584642102" "MDLI"  10521.68 1.00e-06        .  .000012 15126 5 2001 0 1
"20010531" "58446B105" "MPLX"  10726.92 1.00e-06        .  .000012 15126 5 2001 0 1
"20010531" "570619106" "MKTW"  11149.95 1.00e-06        .  .000013 15126 5 2001 0 1
"20010531" "29088W103" "EMRG"   11687.1 1.00e-06        .  .000013 15126 5 2001 0 1
"20010531" "654113109" "NIKU"  11849.94 1.00e-06        .  .000013 15126 5 2001 0 1
"20010531" "741379101" "PRVW"   11901.6 1.00e-06        .  .000013 15126 5 2001 0 1
"20010531" "86149C104" "STOK"  12266.85 1.00e-06        .  .000014 15126 5 2001 0 1
"20010531" "36114Q208" "FTRL"  12276.93 1.00e-06        .  .000014 15126 5 2001 0 1
"20010531" "68338A107" "ONT"    12363.3 1.00e-06        .  .000014 15126 5 2001 0 1
"20010531" "095180105" "BLT"    12500.4 1.00e-06        .  .000014 15126 5 2001 0 1
"20010531" "922815105" "VNTR"   12608.4 1.00e-06        .  .000014 15126 5 2001 0 1
"20010531" "988792107" "ZTEL"     12846 1.00e-06        .  .000014 15126 5 2001 0 1
"20010531" "398081109" "GRIC"  12877.92 1.00e-06        .  .000014 15126 5 2001 0 1
"20010531" "71932A101" "PHGN"  13128.83 1.00e-06        .  .000015 15126 5 2001 0 1
"20010531" "918458100" "VYYO"   13271.4 1.00e-06        .  .000015 15126 5 2001 0 1
"20010531" "925529208" "VYTQE" 13393.64 1.00e-06        .  .000015 15126 5 2001 0 1
"20010531" "00941P106" "ANCC"  13872.87 1.00e-06        .  .000016 15126 5 2001 0 1
"20010531" "284895109" "ELIX"  13917.12 1.00e-06        .  .000016 15126 5 2001 0 1
"20010531" "64122G103" "NTKK"  13950.09 1.00e-06        .  .000016 15126 5 2001 0 1
"20010531" "25385G106" "DIGI"   14780.5 1.00e-06        .  .000017 15126 5 2001 0 1
"20010531" "36155Q109" "GCX"   14802.48 1.00e-06        .  .000017 15126 5 2001 0 1
"20010531" "925653107" "VCNT"  14910.18 1.00e-06        .  .000017 15126 5 2001 0 1
"20010531" "92046N102" "VCLK"  15152.94 1.00e-06        .  .000017 15126 5 2001 0 1
"20010531" "53226T103" "LSPN"  15221.24 1.00e-06        .  .000017 15126 5 2001 0 1
"20010531" "901314104" "TFSM"  15229.69 1.00e-06        .  .000017 15126 5 2001 0 1
"20010531" "037531100" "APRN"   15266.3 1.00e-06        .  .000017 15126 5 2001 0 1
"20010531" "64111K107" "NGRU"  15535.35 1.00e-06        .  .000017 15126 5 2001 0 1
"20010531" "64115A105" "NTWK"  15689.16 1.00e-06        .  .000018 15126 5 2001 0 1
"20010531" "530709104" "LWIRA" 15778.95 1.00e-06        .  .000018 15126 5 2001 0 1
"20010531" "26861P107" "EELN"  15794.67 1.00e-06        .  .000018 15126 5 2001 0 1
"20010531" "83169Q105" "SMDK"   16267.6 1.00e-06        .  .000018 15126 5 2001 0 1
"20010531" "640475109" "NEOF"  16280.19 1.00e-06        .  .000018 15126 5 2001 0 1
"20010531" "18681S106" "CLKS"  16282.07 1.00e-06        .  .000018 15126 5 2001 0 1
"20010531" "46116T100" "IBPI"  16605.06 1.00e-06        .  .000019 15126 5 2001 0 1
"20010531" "74036W102" "PRDS"   16651.8 1.00e-06        .  .000019 15126 5 2001 0 1
"20010531" "64108P101" "NCNT"   16856.7 1.00e-06        .  .000019 15126 5 2001 0 1
"20010531" "008447104" "ACOM"  16964.57 1.00e-06        .  .000019 15126 5 2001 0 1
"20010531" "65332H203" "NEXL"  17328.72 1.00e-06        .  .000019 15126 5 2001 0 1
"20010531" "53220Q105" "LFMN"   17602.2 1.00e-06        .   .00002 15126 5 2001 0 1
"20010531" "83545M109" "SNCI"     17970 1.00e-06        .   .00002 15126 5 2001 0 1
"20010531" "816658108" "SMNS"  18699.84 2.00e-06        .  .000021 15126 5 2001 0 1
"20010531" "516540101" "LNTE"  19084.29 2.00e-06        .  .000021 15126 5 2001 0 1
"20010531" "871045100" "SWBD"  20032.32 2.00e-06        .  .000023 15126 5 2001 0 1
"20010531" "784872301" "SVI"    20048.6 2.00e-06        .  .000023 15126 5 2001 0 1
"20010531" "581243102" "MCKC"  20577.87 2.00e-06        .  .000023 15126 5 2001 0 1
"20010531" "638588103" "NRI"   20597.52 2.00e-06        .  .000023 15126 5 2001 0 1
"20010531" "170404107" "CHRD"   20771.1 2.00e-06        .  .000023 15126 5 2001 0 1
"20010531" "278668108" "EBNX"   20801.2 2.00e-06        .  .000023 15126 5 2001 0 1
"20010531" "03782R108" "APPS"   20880.8 2.00e-06        .  .000023 15126 5 2001 0 1
"20010531" "83545R108" "SOFO"  21019.68 2.00e-06        .  .000024 15126 5 2001 0 1
"20010531" "816288104" "SLTC"  21174.03 2.00e-06        .  .000024 15126 5 2001 0 1
"20010531" "00437W102" "ACRU"   21392.8 2.00e-06        .  .000024 15126 5 2001 0 1
"20010531" "591002100" "METG"     21640 2.00e-06        .  .000024 15126 5 2001 0 1
"20010531" "262506108" "DSLN"  21800.84 2.00e-06        .  .000025 15126 5 2001 0 1
"20010531" "232565101" "CYLK"  21810.36 2.00e-06        .  .000025 15126 5 2001 0 1
"20010531" "63935M109" "NAVI"  21916.38 2.00e-06 2.00e-06        . 15126 5 2001 1 0
"20010531" "315653105" "FTGX"   21948.4 2.00e-06        .  .000025 15126 5 2001 0 1
"20010531" "45677R107" "INFT"   22126.3 2.00e-06        .  .000025 15126 5 2001 0 1
"20010531" "743663304" "POI"   22314.15 2.00e-06        .  .000025 15126 5 2001 0 1
"20010531" "64107U101" "NETP"  22486.55 2.00e-06        .  .000025 15126 5 2001 0 1
end
format %td dt

    I want to create a new variable PROD such that: for every (cusip yr) combination it takes the two observation i) when mn==5 ii) when mn==6 and multiples the r1000 of i) and r2000 of ii) to give PROD. So prod will be a 0/1 since r1000 and r2000 are bytes.
    I am unable to pick the two obervations for multipication. It seems a little complicated.
    So for example for cusip== 216485102 and year ==2001 it will take the observation where mn==5 and multiply its r1000 with the observation where mn==6 to give me PROD. Now I can set a loop for all cusip and year combination in the dataset.

    One way I can think is break it into two datasets for each year, one where mn==5 and one where mn==6. Then merge both of them and thus essentially I will get the columns of the new dataset where mn==5 and mn==6 and then can simply multiple. Finally I can append all the years. But it seems too long and unncessairly complicated.

    Thanks a lot!
    Tags: None
  • #2
    Your example data set contains only mn == 5 observations, so the code could not be properly tested. But here's an approach:
    Code:
    reshape wide r1000_wt r2000_wt, i(cusip date) j(mn)
gen prod = r1000_wt5 * r2000_wt6

    Comment

    • #3
      Hi Clyde thanks for the quick response.
      A small correction I needed it for r1000 and r2000 and not for r1000_wt and r2000_wt. I corrected and code you suggested and implemented it. They are different variables.

      But the data set I am getting is as follows:

      Code:
      
	
      Code:
      
	
      * Example generated by -dataex-. For more info, type help dataex
clear
input str8 date str9 cusip byte(r10005 r20005 r10006 r20006) str6 ticker float(mktvalue r3000_wt r1000_wt r2000_wt) double(dt yr)
"20150529" "000307108" 0 1 . . "AAC"     223445 1.00e-05 .  .000124 20237 2015
"20150630" "000307108" . . 0 1 "AAC"   346737.6  .000015 .  .000184 20269 2015
"20160531" "000307108" 0 1 . . "AAC"   171219.6 8.00e-06 .  .000102 20605 2016
"20160630" "000307108" . . 0 1 "AAC"  223567.55 1.00e-05 .  .000138 20635 2016
"20170531" "000307108" 0 1 . . "AAC"   61525.16 2.00e-06 .  .000033 20970 2017
"20170630" "000307108" . . 0 1 "AAC"   78579.27 3.00e-06 .  .000041 21000 2017
"20180531" "000307108" 0 1 . . "AAC"  122007.64 4.00e-06 .  .000055 21335 2018
"20180629" "000307108" . . 0 1 "AAC"  122203.54 4.00e-06 .  .000056 21364 2018
"20190531" "000307108" 0 1 . . "AAC"    11739.1        0 . 6.00e-06 21700 2019
"20190628" "000307108" . . 0 1 "AAC"   11216.12        0 . 6.00e-06 21728 2019
"20020628" "000360206" . . 0 1 "AAON" 176344.16  .000017 .  .000243 15519 2002
"20030530" "000360206" 0 1 . . "AAON" 144025.34  .000015 .  .000209 15855 2003
"20030630" "000360206" . . 0 1 "AAON" 168420.88  .000017 .  .000236 15886 2003
"20040528" "000360206" 0 1 . . "AAON" 174241.05  .000015 .  .000193 16219 2004
"20040630" "000360206" . . 0 1 "AAON" 175996.23  .000014 .   .00017 16252 2004
"20050531" "000360206" 0 1 . . "AAON"  156034.3  .000012 .  .000144 16587 2005
"20050630" "000360206" . . 0 1 "AAON" 156160.63  .000012 .  .000135 16617 2005
"20060531" "000360206" 0 1 . . "AAON" 204702.95  .000015 .   .00016 16952 2006
"20060630" "000360206" . . 0 1 "AAON" 240280.23  .000017 .  .000188 16982 2006
"20070531" "000360206" 0 1 . . "AAON" 278204.44  .000017 .  .000196 17317 2007
"20070629" "000360206" . . 0 1 "AAON"  293848.1  .000017 .  .000203 17346 2007
"20080530" "000360206" 0 1 . . "AAON" 290895.78  .000019 .  .000239 17682 2008
"20080630" "000360206" . . 0 1 "AAON"  258276.6  .000018 .  .000246 17713 2008
"20090529" "000360206" 0 1 . . "AAON"    278928  .000029 .  .000372 18046 2009
"20090630" "000360206" . . 0 1 "AAON"  248521.9  .000025 .  .000332 18078 2009
"20100528" "000360206" 0 1 . . "AAON"  307907.7  .000026 .   .00031 18410 2010
"20100630" "000360206" . . 0 1 "AAON"  288134.9  .000025 .  .000308 18443 2010
"20110531" "000360206" 0 1 . . "AAON"  416565.7  .000027 .  .000327 18778 2011
"20110630" "000360206" . . 0 1 "AAON"    410177  .000027 .  .000327 18808 2011
"20120531" "000360206" 0 1 . . "AAON"  351955.9  .000024 .  .000315 19144 2012
"20120629" "000360206" . . 0 1 "AAON"    350308  .000023 .  .000305 19173 2012
"20130531" "000360206" 0 1 . . "AAON"    617732  .000034 .  .000444 19509 2013
"20130628" "000360206" . . 0 1 "AAON" 614758.75  .000035 .  .000447 19537 2013
"20140530" "000360206" 0 1 . . "AAON"  875721.6  .000041 .  .000538 19873 2014
"20140630" "000360206" . . 0 1 "AAON"  944023.8  .000043 .  .000537 19904 2014
"20150529" "000360206" 0 1 . . "AAON"  999915.5  .000043 .  .000555 20237 2015
"20150630" "000360206" . . 0 1 "AAON"    921766   .00004 .  .000488 20269 2015
"20160531" "000360206" 0 1 . . "AAON" 1123146.6   .00005 .   .00067 20605 2016
"20160630" "000360206" . . 0 1 "AAON" 1098116.6   .00005 .   .00068 20635 2016
"20170531" "000360206" 0 1 . . "AAON" 1443997.5  .000058 .  .000786 20970 2017
"20170630" "000360206" . . 0 1 "AAON" 1511181.6   .00006 .  .000788 21000 2017
"20180531" "000360206" 0 1 . . "AAON" 1250774.5  .000045 .  .000567 21335 2018
"20180629" "000360206" . . 0 1 "AAON" 1359459.5  .000049 .  .000624 21364 2018
"20190531" "000360206" 0 1 . . "AAON" 1855815.5  .000068 .  .000977 21700 2019
"20190628" "000360206" . . 0 1 "AAON" 2051659.5   .00007 .  .001011 21728 2019
"20200529" "000360206" 0 1 . . "AAON" 2205314.8  .000075 .  .001269 22064 2020
"20200630" "000360206" . . 0 1 "AAON" 2208354.3  .000073 .   .00126 22096 2020
"20210630" "000360206" . . 0 1 "AAON"         .  .000059 .  .000867 22461 2021
"20010531" "000361105" 0 1 . . "AIR"     377048  .000031 .  .000424 15126 2001
"20010629" "000361105" . . 0 1 "AIR"   410793.3  .000034 .   .00048 15155 2001
"20020531" "000361105" 0 1 . . "AIR"   274102.4  .000025 .  .000338 15491 2002
"20020628" "000361105" . . 0 1 "AIR"   260956.8  .000026 .   .00036 15519 2002
"20030530" "000361105" 0 1 . . "AIR"     115128  .000012 .  .000167 15855 2003
"20030630" "000361105" . . 0 1 "AIR"     224861  .000022 .  .000316 15886 2003
"20040528" "000361105" 0 1 . . "AIR"     305123  .000026 .  .000338 16219 2004
"20040630" "000361105" . . 0 1 "AIR"  365980.75   .00003 .  .000353 16252 2004
"20050531" "000361105" 0 1 . . "AIR"   517209.8   .00004 .  .000477 16587 2005
"20050630" "000361105" . . 0 1 "AIR"     509601  .000039 .   .00044 16617 2005
"20060531" "000361105" 0 1 . . "AIR"   875283.9  .000062 .  .000683 16952 2006
"20060630" "000361105" . . 0 1 "AIR"     810528  .000057 .  .000634 16982 2006
"20070531" "000361105" 0 1 . . "AIR"  1184982.5  .000071 .  .000836 17317 2007
"20070629" "000361105" . . 0 1 "AIR"    1231075  .000073 .   .00085 17346 2007
"20080530" "000361105" 0 1 . . "AIR"   719028.3  .000047 .  .000592 17682 2008
"20080630" "000361105" . . 0 1 "AIR"   524125.1  .000038 .    .0005 17713 2008
"20090529" "000361105" 0 1 . . "AIR"   569448.6  .000058 .  .000759 18046 2009
"20090630" "000361105" . . 0 1 "AIR"   621086.9  .000063 .   .00083 18078 2009
"20100528" "000361105" 0 1 . . "AIR"   762330.9  .000064 .  .000767 18410 2010
"20100630" "000361105" . . 0 1 "AIR"     653278  .000056 .  .000698 18443 2010
"20110531" "000361105" 0 1 . . "AIR"  1029869.8  .000068 .  .000807 18778 2011
"20110630" "000361105" . . 0 1 "AIR"  1076529.5  .000072 .  .000859 18808 2011
"20120531" "000361105" 0 1 . . "AIR"   478854.9  .000033 .  .000429 19144 2012
"20120629" "000361105" . . 0 1 "AIR"     543082  .000036 .  .000472 19173 2012
"20130531" "000361105" 0 1 . . "AIR"     808177  .000045 .  .000581 19509 2013
"20130628" "000361105" . . 0 1 "AIR"     885530   .00005 .  .000644 19537 2013
"20140530" "000361105" 0 1 . . "AIR"   965511.9  .000045 .  .000594 19873 2014
"20140630" "000361105" . . 0 1 "AIR"    1090520   .00005 .  .000621 19904 2014
"20150529" "000361105" 0 1 . . "AIR"  1168868.3   .00005 .  .000649 20237 2015
"20150630" "000361105" . . 0 1 "AIR"  1130588.3  .000049 .  .000599 20269 2015
"20160531" "000361105" 0 1 . . "AIR"   865944.8  .000039 .  .000516 20605 2016
"20160630" "000361105" . . 0 1 "AIR"   762447.8  .000035 .  .000472 20635 2016
"20170531" "000361105" 0 1 . . "AIR"    1141385  .000046 .  .000621 20970 2017
"20170630" "000361105" . . 0 1 "AIR"  1107175.5  .000044 .  .000577 21000 2017
"20180531" "000361105" 0 1 . . "AIR"  1440269.4  .000052 .  .000653 21335 2018
"20180629" "000361105" . . 0 1 "AIR"    1496885  .000054 .  .000687 21364 2018
"20190531" "000361105" 0 1 . . "AIR"   980091.5  .000036 .  .000516 21700 2019
"20190628" "000361105" . . 0 1 "AIR"  1198323.9  .000041 .   .00059 21728 2019
"20200529" "000361105" 0 1 . . "AIR"   666578.2  .000023 .  .000384 22064 2020
"20200630" "000361105" . . 0 1 "AIR"     686058  .000023 .  .000392 22096 2020
"20210630" "000361105" . . 0 1 "AIR"          .   .00003 .  .000435 22461 2021
"20020628" "000400101" . . 0 1 "ABCB" 147098.31  .000015 .  .000203 15519 2002
"20030530" "000400101" 0 1 . . "ABCB" 141961.23  .000014 .  .000206 15855 2003
"20030630" "000400101" . . 0 1 "ABCB" 139846.47  .000014 .  .000196 15886 2003
"20040528" "000400101" 0 1 . . "ABCB" 191959.53  .000016 .  .000213 16219 2004
"20040630" "000400101" . . 0 1 "ABCB"  198620.1  .000016 .  .000192 16252 2004
"20050531" "000400101" 0 1 . . "ABCB"  207994.5  .000016 .  .000192 16587 2005
"20050630" "000400101" . . 0 1 "ABCB"  214320.3  .000016 .  .000185 16617 2005
"20050630" "00080S101" . . 0 1 "ABXA"  474900.5  .000036 .   .00041 16617 2005
"20060531" "00080S101" 0 1 . . "ABXA"  392739.8  .000028 .  .000307 16952 2006
"20060630" "00080S101" . . 0 1 "ABXA"  351950.8  .000025 .  .000275 16982 2006
"20070531" "00080S101" 0 1 . . "ABXA"  368266.4  .000022 .   .00026 17317 2007
end
format %td dt
      As you can see I cannot multiply r10006*r20005. It gives me an empty cell.
      Last edited by Dev Irani; 01 Oct 2023, 21:21. Reason: Edited the last line

      Comment

      • #4
        I think you must have introduced some errors when you modified the code I gave you. I have taken your data example from #1 and modified it so that there are pairs of mn = 5 and mn = 6 observations for each cusip-yr combination, adding in random values for r1000 and r2000 in the mn == 6 observations. I have also modified the code to act on r1000 and r2000. It works fine with this example data:
        Code:
        * Example generated by -dataex-. For more info, type help dataex
clear
input str8 date str9 cusip str6 ticker float(mktvalue r3000_wt r1000_wt r2000_wt) double(dt mn yr) byte(r1000 r2000)
"20010531" "00437W102" "ACRU"   21392.8 2.00e-06 .  .000024 15126 5 2001 0 1
"20010531" "00437W102" "ACRU"   21392.8 2.00e-06 .  .000024 15126 6 2001 1 1
"20010531" "008447104" "ACOM"  16964.57 1.00e-06 .  .000019 15126 5 2001 0 1
"20010531" "008447104" "ACOM"  16964.57 1.00e-06 .  .000019 15126 6 2001 0 1
"20010531" "00941P106" "ANCC"  13872.87 1.00e-06 .  .000016 15126 5 2001 0 1
"20010531" "00941P106" "ANCC"  13872.87 1.00e-06 .  .000016 15126 6 2001 1 1
"20010531" "037531100" "APRN"   15266.3 1.00e-06 .  .000017 15126 5 2001 0 1
"20010531" "037531100" "APRN"   15266.3 1.00e-06 .  .000017 15126 6 2001 1 0
"20010531" "03782R108" "APPS"   20880.8 2.00e-06 .  .000023 15126 5 2001 0 1
"20010531" "03782R108" "APPS"   20880.8 2.00e-06 .  .000023 15126 6 2001 0 0
"20010531" "03828R104" "ATHY"    7004.7 1.00e-06 . 8.00e-06 15126 5 2001 0 1
"20010531" "03828R104" "ATHY"    7004.7 1.00e-06 . 8.00e-06 15126 6 2001 1 0
"20010531" "053566105" "AVEA"    6277.2 1.00e-06 . 7.00e-06 15126 5 2001 0 1
"20010531" "053566105" "AVEA"    6277.2 1.00e-06 . 7.00e-06 15126 6 2001 1 1
"20010531" "095180105" "BLT"    12500.4 1.00e-06 .  .000014 15126 5 2001 0 1
"20010531" "095180105" "BLT"    12500.4 1.00e-06 .  .000014 15126 6 2001 1 0
"20010531" "106372105" "BWAYE"  5130.06        0 . 6.00e-06 15126 5 2001 0 1
"20010531" "106372105" "BWAYE"  5130.06        0 . 6.00e-06 15126 6 2001 0 1
"20010531" "124269101" "BUYX"    4503.6        0 . 5.00e-06 15126 5 2001 0 1
"20010531" "124269101" "BUYX"    4503.6        0 . 5.00e-06 15126 6 2001 1 0
"20010531" "12476Q102" "CAIS"   4340.82        0 . 5.00e-06 15126 5 2001 0 1
"20010531" "12476Q102" "CAIS"   4340.82        0 . 5.00e-06 15126 6 2001 1 0
"20010531" "12500B105" "CBIS"   8273.43 1.00e-06 . 9.00e-06 15126 5 2001 0 1
"20010531" "12500B105" "CBIS"   8273.43 1.00e-06 . 9.00e-06 15126 6 2001 0 0
"20010531" "12877Q107" "CALD"   9389.38 1.00e-06 .  .000011 15126 5 2001 0 1
"20010531" "12877Q107" "CALD"   9389.38 1.00e-06 .  .000011 15126 6 2001 0 1
"20010531" "129897104" "CLIC"   5634.75        0 . 6.00e-06 15126 5 2001 0 1
"20010531" "129897104" "CLIC"   5634.75        0 . 6.00e-06 15126 6 2001 1 1
"20010531" "170404107" "CHRD"   20771.1 2.00e-06 .  .000023 15126 5 2001 0 1
"20010531" "170404107" "CHRD"   20771.1 2.00e-06 .  .000023 15126 6 2001 0 0
"20010531" "18681S106" "CLKS"  16282.07 1.00e-06 .  .000018 15126 5 2001 0 1
"20010531" "18681S106" "CLKS"  16282.07 1.00e-06 .  .000018 15126 6 2001 1 1
"20010531" "216485102" "CSAV"   1000.96        0 . 1.00e-06 15126 5 2001 0 1
"20010531" "216485102" "CSAV"   1000.96        0 . 1.00e-06 15126 6 2001 0 0
"20010531" "232565101" "CYLK"  21810.36 2.00e-06 .  .000025 15126 5 2001 0 1
"20010531" "232565101" "CYLK"  21810.36 2.00e-06 .  .000025 15126 6 2001 0 0
"20010531" "232743104" "CYCO"   4856.04        0 . 5.00e-06 15126 5 2001 0 1
"20010531" "232743104" "CYCO"   4856.04        0 . 5.00e-06 15126 6 2001 0 1
"20010531" "23437N104" "DALN"  10517.03 1.00e-06 .  .000012 15126 5 2001 0 1
"20010531" "23437N104" "DALN"  10517.03 1.00e-06 .  .000012 15126 6 2001 1 0
"20010531" "24783N102" "DDDC"   8805.55 1.00e-06 . 1.00e-05 15126 5 2001 0 1
"20010531" "24783N102" "DDDC"   8805.55 1.00e-06 . 1.00e-05 15126 6 2001 0 1
"20010531" "25385G106" "DIGI"   14780.5 1.00e-06 .  .000017 15126 5 2001 0 1
"20010531" "25385G106" "DIGI"   14780.5 1.00e-06 .  .000017 15126 6 2001 1 1
"20010531" "262504103" "DSET"   9156.42 1.00e-06 . 1.00e-05 15126 5 2001 0 1
"20010531" "262504103" "DSET"   9156.42 1.00e-06 . 1.00e-05 15126 6 2001 0 1
"20010531" "262506108" "DSLN"  21800.84 2.00e-06 .  .000025 15126 5 2001 0 1
"20010531" "262506108" "DSLN"  21800.84 2.00e-06 .  .000025 15126 6 2001 1 0
"20010531" "26861P107" "EELN"  15794.67 1.00e-06 .  .000018 15126 5 2001 0 1
"20010531" "26861P107" "EELN"  15794.67 1.00e-06 .  .000018 15126 6 2001 0 0
"20010531" "278668108" "EBNX"   20801.2 2.00e-06 .  .000023 15126 5 2001 0 1
"20010531" "278668108" "EBNX"   20801.2 2.00e-06 .  .000023 15126 6 2001 1 1
"20010531" "284895109" "ELIX"  13917.12 1.00e-06 .  .000016 15126 5 2001 0 1
"20010531" "284895109" "ELIX"  13917.12 1.00e-06 .  .000016 15126 6 2001 1 1
"20010531" "29088W103" "EMRG"   11687.1 1.00e-06 .  .000013 15126 5 2001 0 1
"20010531" "29088W103" "EMRG"   11687.1 1.00e-06 .  .000013 15126 6 2001 0 0
"20010531" "29426X102" "EDG"    2974.16        0 . 3.00e-06 15126 5 2001 0 1
"20010531" "29426X102" "EDG"    2974.16        0 . 3.00e-06 15126 6 2001 0 0
"20010531" "294352109" "EPRS"    3232.8        0 . 4.00e-06 15126 5 2001 0 1
"20010531" "294352109" "EPRS"    3232.8        0 . 4.00e-06 15126 6 2001 1 1
"20010531" "315653105" "FTGX"   21948.4 2.00e-06 .  .000025 15126 5 2001 0 1
"20010531" "315653105" "FTGX"   21948.4 2.00e-06 .  .000025 15126 6 2001 1 1
"20010531" "318224102" "FIRE"   9012.99 1.00e-06 . 1.00e-05 15126 5 2001 0 1
"20010531" "318224102" "FIRE"   9012.99 1.00e-06 . 1.00e-05 15126 6 2001 0 1
"20010531" "36114Q208" "FTRL"  12276.93 1.00e-06 .  .000014 15126 5 2001 0 1
"20010531" "36114Q208" "FTRL"  12276.93 1.00e-06 .  .000014 15126 6 2001 1 0
"20010531" "36155Q109" "GCX"   14802.48 1.00e-06 .  .000017 15126 5 2001 0 1
"20010531" "36155Q109" "GCX"   14802.48 1.00e-06 .  .000017 15126 6 2001 1 0
"20010531" "37243R208" "GNSL"   5238.09        0 . 6.00e-06 15126 5 2001 0 1
"20010531" "37243R208" "GNSL"   5238.09        0 . 6.00e-06 15126 6 2001 1 1
"20010531" "37937R209" "GLBN"   8175.42 1.00e-06 . 9.00e-06 15126 5 2001 0 1
"20010531" "37937R209" "GLBN"   8175.42 1.00e-06 . 9.00e-06 15126 6 2001 0 0
"20010531" "398081109" "GRIC"  12877.92 1.00e-06 .  .000014 15126 5 2001 0 1
"20010531" "398081109" "GRIC"  12877.92 1.00e-06 .  .000014 15126 6 2001 0 0
"20010531" "45073P101" "IBEM"   9046.11 1.00e-06 . 1.00e-05 15126 5 2001 0 1
"20010531" "45073P101" "IBEM"   9046.11 1.00e-06 . 1.00e-05 15126 6 2001 1 0
"20010531" "45677R107" "INFT"   22126.3 2.00e-06 .  .000025 15126 5 2001 0 1
"20010531" "45677R107" "INFT"   22126.3 2.00e-06 .  .000025 15126 6 2001 1 1
"20010531" "46116T100" "IBPI"  16605.06 1.00e-06 .  .000019 15126 5 2001 0 1
"20010531" "46116T100" "IBPI"  16605.06 1.00e-06 .  .000019 15126 6 2001 1 0
"20010531" "516540101" "LNTE"  19084.29 2.00e-06 .  .000021 15126 5 2001 0 1
"20010531" "516540101" "LNTE"  19084.29 2.00e-06 .  .000021 15126 6 2001 0 0
"20010531" "530709104" "LWIRA" 15778.95 1.00e-06 .  .000018 15126 5 2001 0 1
"20010531" "530709104" "LWIRA" 15778.95 1.00e-06 .  .000018 15126 6 2001 1 1
"20010531" "53220Q105" "LFMN"   17602.2 1.00e-06 .   .00002 15126 5 2001 0 1
"20010531" "53220Q105" "LFMN"   17602.2 1.00e-06 .   .00002 15126 6 2001 1 0
"20010531" "53226T103" "LSPN"  15221.24 1.00e-06 .  .000017 15126 5 2001 0 1
"20010531" "53226T103" "LSPN"  15221.24 1.00e-06 .  .000017 15126 6 2001 1 1
"20010531" "538146101" "LPSN"    1903.2        0 . 2.00e-06 15126 5 2001 0 1
"20010531" "538146101" "LPSN"    1903.2        0 . 2.00e-06 15126 6 2001 0 0
"20010531" "545754103" "LOUD"   6412.91 1.00e-06 . 7.00e-06 15126 5 2001 0 1
"20010531" "545754103" "LOUD"   6412.91 1.00e-06 . 7.00e-06 15126 6 2001 0 1
"20010531" "570619106" "MKTW"  11149.95 1.00e-06 .  .000013 15126 5 2001 0 1
"20010531" "570619106" "MKTW"  11149.95 1.00e-06 .  .000013 15126 6 2001 0 0
"20010531" "581243102" "MCKC"  20577.87 2.00e-06 .  .000023 15126 5 2001 0 1
"20010531" "581243102" "MCKC"  20577.87 2.00e-06 .  .000023 15126 6 2001 0 1
"20010531" "58446B105" "MPLX"  10726.92 1.00e-06 .  .000012 15126 5 2001 0 1
"20010531" "58446B105" "MPLX"  10726.92 1.00e-06 .  .000012 15126 6 2001 1 1
"20010531" "584642102" "MDLI"  10521.68 1.00e-06 .  .000012 15126 5 2001 0 1
"20010531" "584642102" "MDLI"  10521.68 1.00e-06 .  .000012 15126 6 2001 1 0
end
format %td dt


reshape wide r1000 r2000, i(cusip yr) j(mn)
gen prod = r10005*r20006
        And to convince you that this works:
        Code:
        . summ r10005 r20006 prod

    Variable |        Obs        Mean    Std. dev.       Min        Max
-------------+---------------------------------------------------------
      r10005 |         50           0           0          0          0
      r20006 |         50         .48     .504672          0          1
        prod |         50           0           0          0          0
        Notice that there are no missing values. It is true that r10005 is, oddly, always zero, and, hence, so is prod, but that is the way it was in the original example data you provided.

        Comment

        • #5
          Hi Clyde, Thanks again for the quick response.
          The i(cusip yr) do not define the dataset as you have used in the prior post (but not in the one before where you used i(cusip date).
          I tried the code
          Code:
           reshape wide r1000 r2000, i(cusip yr) j(mn)
          And it gives me an error -

          Code:
          variable date not constant within cusip yr
variable ticker not constant within cusip yr
variable mktvalue not constant within cusip yr
variable r3000_wt not constant within cusip yr
variable r1000_wt not constant within cusip yr
variable r2000_wt not constant within cusip yr
variable dt not constant within cusip yr
    Your data are currently long. You are performing a reshape wide. You typed something like

        . reshape wide a b, i(cusip yr) j(mn)

    There are variables other than a, b, cusip yr, mn in your data. They must be constant within cusip yr because that is the only way they can fit into wide
    data without loss of information.

    The variable or variables listed above are not constant within cusip yr. Perhaps the values are in error. Type reshape error for a list of the problem
    observations.

    Either that, or the values vary because they should vary, in which case you must either add the variables to the list of xij variables to be reshaped, or
    drop them.
          I then tried using

          Code:
          reshape wide r1000 r2000, i(cusip date) j(mn)
          It gives me on the terminal,

          Code:
          (j = 5 6)

Data                               Long   ->   Wide
-----------------------------------------------------------------------------
Number of observations          121,518   ->   121,518    
Number of variables                  12   ->   13          
j variable (2 values)                mn   ->   (dropped)
xij variables:
                                  r1000   ->   r10005 r10006
                                  r2000   ->   r20005 r20006
-----------------------------------------------------------------------------
          And on the data browser:

          Code:
          * Example generated by -dataex-. For more info, type help dataex
clear
input str8 date str9 cusip byte(r10005 r20005 r10006 r20006) str6 ticker float(mktvalue r3000_wt r1000_wt r2000_wt) double(dt yr)
"20150529" "000307108" 0 1 . . "AAC"     223445 1.00e-05 .  .000124 20237 2015
"20150630" "000307108" . . 0 1 "AAC"   346737.6  .000015 .  .000184 20269 2015
"20160531" "000307108" 0 1 . . "AAC"   171219.6 8.00e-06 .  .000102 20605 2016
"20160630" "000307108" . . 0 1 "AAC"  223567.55 1.00e-05 .  .000138 20635 2016
"20170531" "000307108" 0 1 . . "AAC"   61525.16 2.00e-06 .  .000033 20970 2017
"20170630" "000307108" . . 0 1 "AAC"   78579.27 3.00e-06 .  .000041 21000 2017
"20180531" "000307108" 0 1 . . "AAC"  122007.64 4.00e-06 .  .000055 21335 2018
"20180629" "000307108" . . 0 1 "AAC"  122203.54 4.00e-06 .  .000056 21364 2018
"20190531" "000307108" 0 1 . . "AAC"    11739.1        0 . 6.00e-06 21700 2019
"20190628" "000307108" . . 0 1 "AAC"   11216.12        0 . 6.00e-06 21728 2019
"20020628" "000360206" . . 0 1 "AAON" 176344.16  .000017 .  .000243 15519 2002
"20030530" "000360206" 0 1 . . "AAON" 144025.34  .000015 .  .000209 15855 2003
"20030630" "000360206" . . 0 1 "AAON" 168420.88  .000017 .  .000236 15886 2003
"20040528" "000360206" 0 1 . . "AAON" 174241.05  .000015 .  .000193 16219 2004
"20040630" "000360206" . . 0 1 "AAON" 175996.23  .000014 .   .00017 16252 2004
"20050531" "000360206" 0 1 . . "AAON"  156034.3  .000012 .  .000144 16587 2005
"20050630" "000360206" . . 0 1 "AAON" 156160.63  .000012 .  .000135 16617 2005
"20060531" "000360206" 0 1 . . "AAON" 204702.95  .000015 .   .00016 16952 2006
"20060630" "000360206" . . 0 1 "AAON" 240280.23  .000017 .  .000188 16982 2006
"20070531" "000360206" 0 1 . . "AAON" 278204.44  .000017 .  .000196 17317 2007
"20070629" "000360206" . . 0 1 "AAON"  293848.1  .000017 .  .000203 17346 2007
"20080530" "000360206" 0 1 . . "AAON" 290895.78  .000019 .  .000239 17682 2008
"20080630" "000360206" . . 0 1 "AAON"  258276.6  .000018 .  .000246 17713 2008
"20090529" "000360206" 0 1 . . "AAON"    278928  .000029 .  .000372 18046 2009
"20090630" "000360206" . . 0 1 "AAON"  248521.9  .000025 .  .000332 18078 2009
"20100528" "000360206" 0 1 . . "AAON"  307907.7  .000026 .   .00031 18410 2010
"20100630" "000360206" . . 0 1 "AAON"  288134.9  .000025 .  .000308 18443 2010
"20110531" "000360206" 0 1 . . "AAON"  416565.7  .000027 .  .000327 18778 2011
"20110630" "000360206" . . 0 1 "AAON"    410177  .000027 .  .000327 18808 2011
"20120531" "000360206" 0 1 . . "AAON"  351955.9  .000024 .  .000315 19144 2012
"20120629" "000360206" . . 0 1 "AAON"    350308  .000023 .  .000305 19173 2012
"20130531" "000360206" 0 1 . . "AAON"    617732  .000034 .  .000444 19509 2013
"20130628" "000360206" . . 0 1 "AAON" 614758.75  .000035 .  .000447 19537 2013
"20140530" "000360206" 0 1 . . "AAON"  875721.6  .000041 .  .000538 19873 2014
"20140630" "000360206" . . 0 1 "AAON"  944023.8  .000043 .  .000537 19904 2014
"20150529" "000360206" 0 1 . . "AAON"  999915.5  .000043 .  .000555 20237 2015
"20150630" "000360206" . . 0 1 "AAON"    921766   .00004 .  .000488 20269 2015
"20160531" "000360206" 0 1 . . "AAON" 1123146.6   .00005 .   .00067 20605 2016
"20160630" "000360206" . . 0 1 "AAON" 1098116.6   .00005 .   .00068 20635 2016
"20170531" "000360206" 0 1 . . "AAON" 1443997.5  .000058 .  .000786 20970 2017
"20170630" "000360206" . . 0 1 "AAON" 1511181.6   .00006 .  .000788 21000 2017
"20180531" "000360206" 0 1 . . "AAON" 1250774.5  .000045 .  .000567 21335 2018
"20180629" "000360206" . . 0 1 "AAON" 1359459.5  .000049 .  .000624 21364 2018
"20190531" "000360206" 0 1 . . "AAON" 1855815.5  .000068 .  .000977 21700 2019
"20190628" "000360206" . . 0 1 "AAON" 2051659.5   .00007 .  .001011 21728 2019
"20200529" "000360206" 0 1 . . "AAON" 2205314.8  .000075 .  .001269 22064 2020
"20200630" "000360206" . . 0 1 "AAON" 2208354.3  .000073 .   .00126 22096 2020
"20210630" "000360206" . . 0 1 "AAON"         .  .000059 .  .000867 22461 2021
"20010531" "000361105" 0 1 . . "AIR"     377048  .000031 .  .000424 15126 2001
"20010629" "000361105" . . 0 1 "AIR"   410793.3  .000034 .   .00048 15155 2001
"20020531" "000361105" 0 1 . . "AIR"   274102.4  .000025 .  .000338 15491 2002
"20020628" "000361105" . . 0 1 "AIR"   260956.8  .000026 .   .00036 15519 2002
"20030530" "000361105" 0 1 . . "AIR"     115128  .000012 .  .000167 15855 2003
"20030630" "000361105" . . 0 1 "AIR"     224861  .000022 .  .000316 15886 2003
"20040528" "000361105" 0 1 . . "AIR"     305123  .000026 .  .000338 16219 2004
"20040630" "000361105" . . 0 1 "AIR"  365980.75   .00003 .  .000353 16252 2004
"20050531" "000361105" 0 1 . . "AIR"   517209.8   .00004 .  .000477 16587 2005
"20050630" "000361105" . . 0 1 "AIR"     509601  .000039 .   .00044 16617 2005
"20060531" "000361105" 0 1 . . "AIR"   875283.9  .000062 .  .000683 16952 2006
"20060630" "000361105" . . 0 1 "AIR"     810528  .000057 .  .000634 16982 2006
"20070531" "000361105" 0 1 . . "AIR"  1184982.5  .000071 .  .000836 17317 2007
"20070629" "000361105" . . 0 1 "AIR"    1231075  .000073 .   .00085 17346 2007
"20080530" "000361105" 0 1 . . "AIR"   719028.3  .000047 .  .000592 17682 2008
"20080630" "000361105" . . 0 1 "AIR"   524125.1  .000038 .    .0005 17713 2008
"20090529" "000361105" 0 1 . . "AIR"   569448.6  .000058 .  .000759 18046 2009
"20090630" "000361105" . . 0 1 "AIR"   621086.9  .000063 .   .00083 18078 2009
"20100528" "000361105" 0 1 . . "AIR"   762330.9  .000064 .  .000767 18410 2010
"20100630" "000361105" . . 0 1 "AIR"     653278  .000056 .  .000698 18443 2010
"20110531" "000361105" 0 1 . . "AIR"  1029869.8  .000068 .  .000807 18778 2011
"20110630" "000361105" . . 0 1 "AIR"  1076529.5  .000072 .  .000859 18808 2011
"20120531" "000361105" 0 1 . . "AIR"   478854.9  .000033 .  .000429 19144 2012
"20120629" "000361105" . . 0 1 "AIR"     543082  .000036 .  .000472 19173 2012
"20130531" "000361105" 0 1 . . "AIR"     808177  .000045 .  .000581 19509 2013
"20130628" "000361105" . . 0 1 "AIR"     885530   .00005 .  .000644 19537 2013
"20140530" "000361105" 0 1 . . "AIR"   965511.9  .000045 .  .000594 19873 2014
"20140630" "000361105" . . 0 1 "AIR"    1090520   .00005 .  .000621 19904 2014
"20150529" "000361105" 0 1 . . "AIR"  1168868.3   .00005 .  .000649 20237 2015
"20150630" "000361105" . . 0 1 "AIR"  1130588.3  .000049 .  .000599 20269 2015
"20160531" "000361105" 0 1 . . "AIR"   865944.8  .000039 .  .000516 20605 2016
"20160630" "000361105" . . 0 1 "AIR"   762447.8  .000035 .  .000472 20635 2016
"20170531" "000361105" 0 1 . . "AIR"    1141385  .000046 .  .000621 20970 2017
"20170630" "000361105" . . 0 1 "AIR"  1107175.5  .000044 .  .000577 21000 2017
"20180531" "000361105" 0 1 . . "AIR"  1440269.4  .000052 .  .000653 21335 2018
"20180629" "000361105" . . 0 1 "AIR"    1496885  .000054 .  .000687 21364 2018
"20190531" "000361105" 0 1 . . "AIR"   980091.5  .000036 .  .000516 21700 2019
"20190628" "000361105" . . 0 1 "AIR"  1198323.9  .000041 .   .00059 21728 2019
"20200529" "000361105" 0 1 . . "AIR"   666578.2  .000023 .  .000384 22064 2020
"20200630" "000361105" . . 0 1 "AIR"     686058  .000023 .  .000392 22096 2020
"20210630" "000361105" . . 0 1 "AIR"          .   .00003 .  .000435 22461 2021
"20020628" "000400101" . . 0 1 "ABCB" 147098.31  .000015 .  .000203 15519 2002
"20030530" "000400101" 0 1 . . "ABCB" 141961.23  .000014 .  .000206 15855 2003
"20030630" "000400101" . . 0 1 "ABCB" 139846.47  .000014 .  .000196 15886 2003
"20040528" "000400101" 0 1 . . "ABCB" 191959.53  .000016 .  .000213 16219 2004
"20040630" "000400101" . . 0 1 "ABCB"  198620.1  .000016 .  .000192 16252 2004
"20050531" "000400101" 0 1 . . "ABCB"  207994.5  .000016 .  .000192 16587 2005
"20050630" "000400101" . . 0 1 "ABCB"  214320.3  .000016 .  .000185 16617 2005
"20050630" "00080S101" . . 0 1 "ABXA"  474900.5  .000036 .   .00041 16617 2005
"20060531" "00080S101" 0 1 . . "ABXA"  392739.8  .000028 .  .000307 16952 2006
"20060630" "00080S101" . . 0 1 "ABXA"  351950.8  .000025 .  .000275 16982 2006
"20070531" "00080S101" 0 1 . . "ABXA"  368266.4  .000022 .   .00026 17317 2007
end
format %td dt
          As you can see I cannot multiply r20005*r10006. That will be empty.

          Is there any thing I can do to help you help me better? Like sort the data before sharing the sample data through dataex?

          Comment

          • #6
            You need to show an example of the data that includes several different firms, dates, yrs, and also crosses those with both mn = 5 and mn = 6 observations. And if mn can take on values other than just 5 or 6, some of those need to be included as well. Clearly the organization of your data is different from what I have been imagining. To help you I need to work with a data example that is fully representative of the complexity of your data.
            • 1 like

            Comment

            • #7
              Hi Clyde Schechter, I worked it out in a different way. Definitely longer and inefficient but a much easier logic.

              Code:
              preserve
keep if mn==5
keep cusip yr r1000 r2000
rename (r1000 r2000) (r1000may r2000may)
save ../Data/may,replace
restore

preserve
keep if mn==6
keep cusip yr r1000 r2000
rename (r1000 r2000) (r1000june r2000june)
save ../Data/june, replace
restore

use ../Data/Russellmay, clear
merge 1:1 cusip yr using ../Data/june
keep if _merge==3
drop _merge

generate R2000toR1000 = r2000may*r1000june //Switcher from R2000 to R1000
generate R1000toR2000 = r1000may*r2000june //Switcher from R1000 to R2000
generate R1000toR1000 = r1000may*r1000june //Stayer for R1000
generate R2000toR2000 = r2000may*r2000june //Stayer for R2000

              Comment

              Previous Next
              Working...
              X