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
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
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)

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