Reshape Annual data from wide to long

[ivanpersie]

Hello friends,

My dataset has Annual expense fees for years ranging from 2019 to 2022. I want to convert this to monthly and reshape it from wide to long format for each secid. How can i do this? Here is the sample observations.

----------------------- copy starting from the next line -----------------------

* Example generated by -dataex-. For more info, type help dataex
clear
input str10(secid fundid) float(expratio_2019 expratio_2020 expratio_2021 expratio_2022)
"F000000EU6" "FSUSA08BX4"  .84  .78  .76  .76
"F000002KXA" "FSUSA007UH"   .8  .88  .91  .93
"F000002KYY" "FSUSA0030D"  .95  .95  .95  .92
"F000002KYZ" "FSUSA0030D"  .75  .75  .75  .72
"F000002KZ0" "FSUSA0030D" 1.45 1.44    .    .
"F000002KZ1" "FSUSA0030D" 1.35 1.34    .    .
"F000002L2E" "FSUSA09QKQ" 1.23 1.21  1.2 1.21
"F000002L2I" "FSUSA0039B"  .49  .48  .42  .43
"F000002L2N" "FSUSA09QKQ"  .98  .96  .94  .94
"F000002L2O" "FSUSA09QKQ" 1.95 1.93 1.85 1.88
"F000002L2Q" "FSUSA003XI"  .89  .97  .92  .89
"F000002L2S" "FSUSA068YS"  .85    .    .    .
"F000002LMR" "FSUSA0098N" 1.01  .98  .95  .93
"F000002LMW" "FSUSA077W9"  .79  .78  .78  .87
"F000002LMX" "FSUSA0098K" 1.03 1.04 1.03 1.04
"F000002LWU" "FSUSA0017Z"  1.1 1.08 1.09 1.09
"F000002LWZ" "FSUSA04YQ9" 1.45 1.48 1.29 1.28
"F000002LX0" "FSUSA04CIF" 1.06  .94  .89  .88
"F000002LX2" "FSUSA001DV"    1  .95    .    .
"F000002LZA" "FSUSA09RHM"  .84  .84    .  .84
"F000002LZO" "FSUSA0A1EN"  .87  .88  .89  .89
"F000002LZU" "FSUSA0A1EN"  .87  .88  .89  .89
"F000002LZX" "FSUSA0A1EN" 1.58 1.58 1.58 1.58
"F000002LZZ" "FSUSA0A1EN" 1.58 1.62 1.62    .
"F000002M0A" "FSUSA0001Y"  .36  .34  .33  .33
"F000002M67" "FSUSA09RM8"   .4   .4  .41  .41
"F000002M68" "FSUSA09RM9"   .6   .6  .61  .61
"F000002M69" "FSUSA09RMA"  .55  .55  .56  .58
"F000002M6E" "FSUSA08FWV"  .98 1.01 1.04  .99
"F000002MQS" "FSUSA003FW"  .99  .99  .99  .99
"F000002MQU" "FSUSA001P1"  .88  .88  .88  .88
"F000002MTH" "FSUSA09RT2" 1.24 1.24 1.31 1.31
"F000002MTJ" "FSUSA09RT2"  .92  .94 1.02 1.04
"F000002MTK" "FSUSA09RT2" 1.49 1.49 1.56 1.56
"F000002MTL" "FSUSA09RT2"  .99  .99 1.06 1.06
"F000002MTN" "FSUSA09RT2" 1.99 1.99 2.06 2.06
"F000002N6A" "FSUSA002W9"  1.3 1.34 1.33 1.36
"F000002N6B" "FSUSA002W9" 1.12 1.12 1.12 1.12
"F000002N6D" "FSUSA002W9" 1.83 1.82 1.83 1.85
"F000002N6M" "FSUSA004IT" 1.26 1.26 1.26 1.26
"F000002N6O" "FSUSA004IT" 1.51 1.51 1.51 1.51
"F000002N6P" "FSUSA000GZ" 1.79 1.79 1.79 1.79
"F000002N6T" "FSUSA000GZ" 1.29  1.3 1.29  1.3
"F000002N6U" "FSUSA0015U" 1.09 1.07 1.05 1.05
"F000002N6V" "FSUSA0015U" 1.84 1.81 1.79  1.8
"F000002N6X" "FSUSA0015U" 1.36 1.36 1.36 1.36
"F000002N71" "FSUSA001HM" 1.07 1.06 1.04 1.06
"F000002N72" "FSUSA000GZ" 1.05 1.05 1.04 1.04
"F000002N73" "FSUSA001HM" 1.82 1.81 1.79  1.8
"F000002N78" "FSUSA004IT" 2.01 2.01 2.01 2.01
"F000002N7A" "FSUSA001HM" 1.33 1.31 1.29 1.31
"F000002N7D" "FSUSA072O8" 1.41 1.41 1.42 1.45
"F000002NZW" "FSUSA09SUE"  .98 1.02   .9   .9
"F000002O86" "FSUSA09SZU" 1.49 1.49 1.49 1.49
"F000002OBT" "FSUSA09T3R"  .66  .66  .66  .66
"F000002OBU" "FSUSA09T3R"  .75  .75  .75  .73
"F000002OBV" "FSUSA09T3R"    1    1    1    1
"F000002OLY" "FSUSA0040V"  .25  .24  .24  .25
"F000002OLZ" "FSUSA0017G" 1.48 1.49 1.49 1.47
"F000002OS0" "FSUSA09TWB"    1  .87  .81  .81
"F000002OS1" "FSUSA09TWB"  .78  .65  .59  .59
"F000002OS3" "FSUSA09TWB" 1.47 1.35 1.28 1.28
"F000002OUY" "FSUSA003ON" 1.45 1.47 1.45 1.45
"F000002OV3" "FSUSA003ON"  .84  .85  .83  .86
"F000002OV8" "FSUSA003ON" 1.14 1.14 1.13 1.14
"F000002OVD" "FSUSA09TYT"  .25  .25  .25  .25
"F000002OVE" "FSUSA09TYU"  .25  .25  .25  .25
"F000002P1J" "FSUSA08TR4" 1.75 1.75 1.75 1.75
"F000002P1T" "FSUSA000PI"  .49  .47  .46  .46
"F000002P1U" "FSUSA000NA" 1.01  .85   .8   .8
"F000002P1V" "FSUSA000NB"  .91  .84  .83  .82
"F000002P6H" "FSUSA08IE7" 1.23 1.16 1.14 1.17
"F000002P7X" "FSUSA067P7" 1.16  1.2 1.17 1.03
"F000002P7Y" "FSUSA067P7"  1.8 1.84 1.85 1.68
"F000002P7Z" "FSUSA067P7"  .74  .81  .79  .68
"F000002P80" "FSUSA067P7" 1.18 1.24 1.21 1.12
"F000002P85" "FSUSA001B2"  .77  .74  .74  .71
"F000002P86" "FSUSA001B2" 1.49 1.47 1.32 1.42
"F000002P87" "FSUSA001B2"  .57  .56  .56  .49
"F000002P88" "FSUSA001B2"  .95  .93  .92   .9
"F000002P89" "FSUSA001UR" 1.14 1.39 1.11 1.05
"F000002P8A" "FSUSA001UR" 1.75 1.92 1.77 1.69
"F000002P8B" "FSUSA001UR"  .79 1.01  .81  .74
"F000002P8C" "FSUSA001UR" 1.43 1.61 1.42 1.36
"F000002P8D" "FSUSA001UR" 1.18 1.36 1.18 1.11
"F000002P99" "FSUSA06L2G"  .46  .46  .46  .45
"F000002P9A" "FSUSA06L2G"  1.2 1.18 1.14 1.16
"F000002P9B" "FSUSA06L2G"  .22  .22  .21  .21
"F000002P9C" "FSUSA06L2G"  .61  .62  .62  .62
"F000002P9D" "FSUSA06L2F"  .45  .41  .43  .43
"F000002P9F" "FSUSA06L2F" 1.14 1.18 1.11 1.19
"F000002P9G" "FSUSA06L2F"  .22  .21  .21  .22
"F000002P9H" "FSUSA06L2F"   .6  .62  .62  .62
"F000002P9I" "FSUSA06L2E"  .46  .47 1.15  .47
"F000002P9J" "FSUSA06L2E" 1.18 1.18 1.15 1.18
"F000002P9L" "FSUSA06L2E"  .21  .21  .22  .24
"F000002P9M" "FSUSA06L2E"  .56  .63  .65  .65
"F000002P9N" "FSUSA067P5" 1.12 1.12  1.1 1.12
"F000002P9O" "FSUSA067P5" 1.68  1.7 1.65  1.6
"F000002P9P" "FSUSA067P5"  .76  .76  .75  .76
end

[Nick Cox]

reshape long expratio_, i(secid) j(year)

works with your example data.

[ivanpersie]

Hi Nick, am getting this error.


. reshape long expratio_, i(secid) j(year)
variable year contains all missing values
r(498);

[ivanpersie]

Sorry Nick. It worked though it doesn't distribute the data on a monthly basis. I would like to have it on a monthly not Annual. i.e (annual/12)

[Nick Cox]

I don't know what that implies beyind a need to divide by 12. I was imagining that you know how to do that.

Are you going to merge this with some other dataset? In which case it's a many-to-one merge.

[ivanpersie]

I want breakdown Annual expense fee to monthly. And yes eventually i want to do a "many to one merge"