I have a pooled cross-sectional data that I want to present in a panel data format. Here is a sample of my data
but first let me explain the variables, Date is the month, all the other columns are codes for firm name (the values are the returns for that firm/stock). on the average I have over 200 firms per country; NB: this is for just one country, Germany, I have 7 more.
it will take forever if am to manually copy and paste, in order to have the returns for each stock in each month (in the panel format), so I need a way to convert it more efficiently. thanks
Simply put I want the data to appear in this format. I have other variables that I will be adding to it once I have it in the format below, I can figure those out. (am actually running a fama macbeth 2 step regression: I hinted this incase there is an alternate approach to run it without converting to panel data format)
but first let me explain the variables, Date is the month, all the other columns are codes for firm name (the values are the returns for that firm/stock). on the average I have over 200 firms per country; NB: this is for just one country, Germany, I have 7 more.
it will take forever if am to manually copy and paste, in order to have the returns for each stock in each month (in the panel format), so I need a way to convert it more efficiently. thanks
Code:
* Example generated by -dataex-. To install: ssc install dataex clear input int Date str18 DMTX double DRHM str18 DSFQ double DDAI 16102 "-" .14869987152670872 "-" .03498227770072424 16131 "-" .048443450290235376 "-" -.05985705777248357 16162 "-" .14620599670278336 "-" -.04577130186886285 16192 "-" .07086983609331364 "-" .14257261410788388 16223 "-" -.04539349407801651 "-" -.042417199302730996 16253 "-" .06760366271152019 "-" .0526395631067961 16284 "-" -.0574380514757703 "-" -.027669693039342868 16315 "-" .04531859849561087 "-" -.06625166740773676 16345 "-" .04633663083052579 "-" -.005714285714285706 16376 "-" 0 "-" -.04054916985951467 16406 "-" .11000061525420259 "-" .028785357737104774 16437 "-" .01029118320892767 "-" .04997573993207187 16468 "-" .020384899081338323 "-" -.0056993222427603905 16496 "-" .08888981820571502 "-" -.0048024786986832265 16527 "-" -.03921192103848223 "-" -.028642590286425847 16557 "-" -.05608757473489457 "-" -.06378205128205124 16588 "-" .048306986817667534 "-" .08661417322834637 16618 "-" .014987130804122777 "-" .02079395085066163 16649 "-.0105203066383951" .1123272249671649 "-" .1828530465949821 16680 "-.0698096101541251" .0598461420502466 "-" .018561786085150474 16710 ".1118199539252171" .12731078454541522 "-" .09264687141582766 16741 "-.0637551801083838" -.055557457003508226 "-" -.08432470258922312 16771 ".0761831801157644" -.04050209735723921 "-" .05464272067252569 16802 "-.0139207466582299" .07839462167100211 "-" .010386473429951684 16833 ".0648913130664955" .1278620774194678 "-" .09562514941429597 16861 ".0122024706236819" .07734527839021219 "-" -.013746454287584453 16892 "-.0166691471945231" -.003527292379751383 "-" .016482300884955694 16922 ".0328439533827759" -.01185590680150846 "-" -.06355424964631629 16953 "-.1038980070339976" -.1129926964565374 "-" -.0634514816966879 16983 ".053965658217498" -.01602643433493388 "-" -.049633949621541135 17014 ".0367726920093094" -.07692210944672931 "-" .017756887322104706 17045 "-.0219245734809936" .02999831954909423 "-" .04605516356638875 17075 ".1005278861602019" .08870996190207414 "-" -.049546234976698626 17106 ".094751477233229" -.024305189913406845 "-" .1587096774193548 17136 "-.0291465582931166" -.040680611537519555 "-" -.04677060133630293 17167 ".1409510105304468" .07285559127827222 "-" .08084112149532713 17198 ".142857142857143" .1048245932450855 "-" .03091223519239083 17226 "-.0490068218298556" -.07407420928127109 "-" .05724470538897052 17257 ".1404609947782056" .1653275962676472 "-" .1828530465949821 17287 "-.0086023494588844" .03222521968020319 "-" -.01853357177694685 17318 ".0854637059152827" .018675048447922088 "-" .13914195792612222 17348 ".0261303077187553" -.06203160270880362 "-" .0026901265086520616 17379 ".0632015412966996" -.05043617967681544 "-" -.036183017910231374 17410 "-.0849320464841442" -.0563312335260681 "-.1305678699598901" .022344267228408057 17440 "-.1082698351198933" -.05514847523930885 "-.0894743231759136" .0749871219368609 17471 ".0120208554600754" .03862665992307601 "-.0316000000000001" .03388554216867462 17501 "-.1520297667318609" -.07024676398310369 "-.0557620817843866" -.07124412368403622 17532 ".0966471647164717" -.025424279334145306 ".0352872557596967" -.04690953161759472 17563 "-.0720221606648199" -.09923428790441524 "-.1290140845070422" -.18370857954970457 17592 "-.1081813156440022" -.07350914354956564 "-.0663001293661061" .032804911573352864 17623 "-.1265108783239325" -.008306543399692548 "-.0483200554208521" -.017744654422855118 17653 ".0370422934998581" .07559649652672923 ".0223839854413102" -.05473760274591264 17684 "-.0784863829204872" .06022145471223047 ".1901032395870416" -.030291447682752045 17714 "-.2426672607113685" -.12910843713859438 "-.132814836972779" -.20683878596767843 17745 "-.0392195313266007" -.11778182131507327 "-.1586754053121766" -.042986706423158084 17776 ".211438544080693" .13531044071263193 "-.1213612136121362" .10658185122679467 17806 "-.178450907922224" -.20431554923561948 "-.2186187587494166" -.18864382918817454 17837 "-.1771149144254279" -.28445139789870005 "-.7823230815168708" -.19144013880855978 17867 "-.0572854765866414" -.3102035374633365 ".037037037037037" -.14234620886981403 17898 ".211438544080693" .1653275962676472 "-.123015873015873" .1551292743953294 17929 ".1268193384223918" .08875351360880691 "-.2232277526395173" -.19314079422382666 17957 "-.0866227079757925" .04317905815679395 "-.5300970873786408" -.1807606263982104 17988 "-.0943433544303798" .007347044366834804 "1.042285155021619" .11305297651556528 18018 ".211438544080693" .1653275962676472 ".6377749029754204" .1828530465949821 18049 "-.0472001223990208" -.0016751466622182013 "-.0821484992101107" .03703703703703714 18079 ".1287033319951828" .034422497162789764 ".1273666092943202" -.05714285714285722 18110 "-.0299473609332765" .11568879525614008 ".4389312977099238" .1828530465949821 18141 ".1292073036591626" -.044884440663855274 "-.6132625994694961" -.09396991858700154 18171 ".130333138515488" .1653275962676472 ".5706447187928668" .09503502893694805 18202 "-.0367114788004137" -.0890596378608829 ".211353711790393" -.028094575799721976 18232 ".1295401681875112" .15083785627428312 ".2804614275414566" .0827132226674299 18263 ".0688526321347485" .07863865494242679 "-.1762387387387388" .056304520222046066 18294 "-.0148693375487824" .04073187398158358 "-.1196172248803828" -.09547047047047041 18322 ".0196068598936918" .029187997887922205 "-.0939440993788821" -.06902752801217331 18353 ".1479368514237938" .1266818944287805 ".1456726649528705" .12451708766716212 18383 "-.0304185767533524" .0013027226507436067 "1.042285155021619" .10795454545454528 18414 ".0593875657284256" -.16116926410863777 ".0192098586444364" .048896839594514116 18444 ".0224400417101147" .02567402567402569 "-.0430298719772404" -.0019329164297896726 18475 ".005344103128952" .02775237053413436 ".234113712374582" .013670539986329493 18506 "-.0024346696964778" -.03610831600366728 "-.0593194820837098" -.027421892560125845 18536 "-.0779775463716239" .08107882392788045 "0" .12537554887913097 18567 ".0519257069748975" .06829437868552746 "-.0345710627400768" .04938905431769178 18597 ".055569535312867" -.01864461933400016 ".0175729442970823" .09207436399217217 end format %tdnn/dd/CCYY Date
Code:
* Example generated by -dataex-. To install: ssc install dataex clear input int Date str5 firm str18 returns 16102 "D:MTX" "-.5833317982536934" 16131 "D:MTX" ".6017778260547225" 16162 "D:MTX" "-.56519469614012" 16192 "D:MTX" ".3299840801732912" 16223 "D:MTX" ".0998704891976217" 16253 "D:MTX" "-.109365661323271" 16284 "D:MTX" "-.1848639336545106" 16315 "D:MTX" ".0995035879288312" 16345 "D:MTX" "0" 16376 "D:MTX" "0" 16406 "D:MTX" "0" 16437 "D:MTX" "-.2416575400639235" 16468 "D:MTX" "0" 16496 "D:MTX" "0" 16527 "D:MTX" "0" 16557 "D:MTX" "0" 16588 "D:MTX" "0" 16618 "D:MTX" "0" 16649 "D:MTX" "0" 16680 "D:MTX" "-.0666686315541278" 16710 "D:MTX" ".0714308270439259" 16741 "D:MTX" "0" 16771 "D:MTX" ".6017778260547225" 16802 "D:MTX" "-.2708337445148388" 16833 "D:MTX" "0" 16861 "D:MTX" "0" 16892 "D:MTX" "0" 16922 "D:MTX" "0" 16953 "D:MTX" ".6017778260547225" 16983 "D:MTX" "-.2710025752401162" 17014 "D:MTX" ".3717469582685819" 17045 "D:MTX" "0" 17075 "D:MTX" "-.4682515998541065" 17106 "D:MTX" "0" 17136 "D:MTX" ".6017778260547225" 17167 "D:MTX" "0" 17198 "D:MTX" ".4782577355752367" 17226 "D:MTX" ".3088248201520701" 17257 "D:MTX" "-.1091023341993234" 17287 "D:MTX" "-.3113871116129302" 17318 "D:MTX" ".1172136258006008" 17348 "D:MTX" ".0734433999869544" 17379 "D:MTX" "0" 17410 "D:MTX" ".3013155718196887" 17440 "D:MTX" "0" 17471 "D:MTX" ".0046928869945548" 17501 "D:MTX" "-.1472968297330655" 17532 "D:MTX" "0" 17563 "D:MTX" ".1672632025193798" 17592 "D:MTX" "0" 17623 "D:MTX" "0" 17653 "D:MTX" "0" 17684 "D:MTX" "0" 17714 "D:MTX" "0" 17745 "D:MTX" "0" 17776 "D:MTX" "0" 17806 "D:MTX" "-.1374271357305364" 17837 "D:MTX" "-.104354259523251" 17867 "D:MTX" "0" 17898 "D:MTX" ".0633470336194276" 17929 "D:MTX" "0" 17957 "D:MTX" "0" 17988 "D:MTX" "0" 18018 "D:MTX" "0" 18049 "D:MTX" "0" 18079 "D:MTX" "0" 18110 "D:MTX" "0" 18141 "D:MTX" ".1000003157871469" 18171 "D:MTX" ".1024671581461577" 18202 "D:MTX" "0" 18232 "D:MTX" "0" 18263 "D:MTX" "-.1084944105783678" 18294 "D:MTX" "0" 18322 "D:MTX" "0" 18353 "D:MTX" "0" 18383 "D:MTX" "0" 18414 "D:MTX" "0" 18444 "D:MTX" "0" 18475 "D:MTX" "0" 18506 "D:MTX" "0" 18536 "D:MTX" "0" 18567 "D:MTX" ".0306574289352853" 18597 "D:MTX" "0" 16102 "D:RHM" "-.0627988007454825" 16131 "D:RHM" ".0621649662804772" 16162 "D:RHM" "-.0406186406186406" 16192 "D:RHM" ".2005695974536714" 16223 "D:RHM" "-.0792183231771381" 16253 "D:RHM" ".1446067244789289" 16284 "D:RHM" ".0023260450588157" 16315 "D:RHM" "-.0364673120275825" 16345 "D:RHM" ".011010184420589" 16376 "D:RHM" ".0281786005989655" 16406 "D:RHM" ".0265457434132132" 16437 "D:RHM" "-.0651963629328689" 16468 "D:RHM" ".1025110375275937" 16496 "D:RHM" ".015454886747591" 16527 "D:RHM" "-.0222441308768253" 16557 "D:RHM" "-.0525586085202924" 16588 "D:RHM" ".0832779034189172" end format %tdnn/dd/CCYY Date
