Reshaping Dataset to long format

[Ryan Marapin]

Dear STATA forum,

I am currently working with time series data, which looks as follows:

* Example generated by -dataex-. To install: ssc install dataex
clear
input str9 A str18(C D E F H I J N AV GZ)
"group"     "1"                  "1"                  "1"                  "1"                  "1"                  "1"                  "1"                  "1"                  "3"                  "6"                 
"series"    "PCECC96"            "PCDGx"              "PCESVx"             "PCNDx"              "FPIx"               "Y033RC1Q027SBEAx"   "PNFIx"              "A823RL1Q225SBEA"    "USLAH"              "CPIAPPSL"          
"01oct1959" ".3650269129167977"  ".216028379993192"   "1.179607709471712"  "-.2967423205396629" ".0495683544804948"  ".6394198594864074"  ".7376720827380208"  ".5515294829904713"  "-.2028341548527938" ".4274368075580418" 
"01jan1960" "-1.038272296385466" "-2.1248980673514"   ".8717495172530111"  ".0286256548641881"  "-1.00154327243243"  "-.5419068647278947" "-.7082569079974281" "-.8071674360430571" "-.1034524944867727" "-.6157613669459645"
"01apr1960" ".2330519053439989"  ".6462049897425028"  ".2170887836461679"  "-.488438864473532"  "1.10563026070154"   ".7140166687668444"  "1.111214670973889"  "-2.311960797983416" ".9112059543879397"  ".1152341626848988" 
"01jul1960" ".6894363614418856"  ".3311574486157206"  ".6445833730489374"  ".7052953694152898"  "-1.317474119359146" ".1077035370398261"  ".1432600855863257"  "-.1059045100902682" ".7672618455065779"  ".3209967556727604" 
"01oct1960" "-1.815167574724039" "-.7182466608899543" "-2.010713804545575" "-1.681672688885647" "-1.474786397559528" "-2.001673458551451" "-1.439875396154446" "1.647252804791704"  "-1.942275301868003" "-.1968986016941461"
"01jan1961" "-1.012717500919827" "-1.31699927459115"  ".3440066616958974"  "-.7468123683105136" "-.5439891800339417" "-1.711734072929461" "-.686485749690427"  "-.0036370000554865" "-1.343913507751506" "-.1940148721752263"
"01apr1961" "-1.265030523659988" "-2.385659931921413" ".3818564291751042"  ".0017701771616262"  "-.852752046639895"  "-1.619007477152278" "-1.278199415199424" "-.0474659329275358" "-1.447116222993477" "-.0904296547895576"
"01jul1961" "1.028181956482924"  ".2635462626236992"  "1.395624122178266"  "1.047319935244806"  ".320390512806864"   "1.149376185431282"  ".5150877214902447"  ".2885558857581755"  "-1.447489192686121" "-.2946968534090217"
"01oct1961" "-.4741309717936375" ".386784411896169"   "-1.090030243182865" "-.7327062177342363" ".6740182985056374"  "-.0027749604162845" "-.0916161881577133" "1.603423871919655"  "-.0916305019058138" ".6254159245173585" 
"01jan1962" "1.806877163511684"  "1.184793236978319"  "2.084363705545158"  "1.073205459692264"  "1.023205479575812"  "1.553996781663094"  ".9053877652513282"  ".8875513016761827"  "-.0133912208794087" "-.4983635406068285"
"01apr1962" ".3868891877851496"  ".2951560576894264"  ".274277039405609"   ".3271455521052388"  ".4474998613964707"  ".4094108092063287"  ".4039526081671853"  "2.05632284493083"   ".0632629931201929"  "-.089538963428239" 
"01jul1962" ".6415112640529802"  ".4690597982810318"  "1.501809979182293"  "-.2810237129128761" ".8741803317765729"  ".3949598143762736"  ".7443037232352402"  "-.0036370000554865" ".6399187051039077"  ".5219950427953112" 
"01oct1962" ".006811979715034"   "-.1923909169116167" ".0782071631553874"  ".2550478480296683"  "-.0685263180852191" "-.3477472084749097" ".0347941242230708"  "1.588814227628971"  "-.6227203074822057" ".1128955788980066" 
"01jan1963" ".918796140849536"   "1.038266785708293"  ".9696831591242979"  "-.050460291815162"  "-.5928637444044654" "-.3687737690927571" "-.7541435508185592" ".0401919328165628"  "-.2432983148115164" "-.0907155024708925"
"01apr1963" "-.1516759755249011" ".2355007054356036"  "-.7883935713899857" "-.0635079564150938" ".1651988841451537"  ".1628581060821312"  "-.4546943747951009" "-2.122035422204536" "-.3306680589379105" ".0115499511787685" 
"01jul1963" ".2134739499711086"  ".2871131404648894"  ".571081293208995"   "-.3612698975652616" "1.614521979741091"  ".2188935600813344"  ".936591227504702"   "-.500364905938712"  ".0256149179170723"  "-.0895414987890042"
"01oct1963" ".8317360486024667"  "-.0199021398544998" "2.002772276961568"  ".4465403993343057"  ".7029652784007221"  ".991078706458812"   ".7637052394371432"  "3.356581186801627"  "-.3731621487491961" ".3126809887864598" 
"01jan1964" ".0478190578910472"  ".0960184847336646"  "1.21071907016694"   "-1.01904394343553"  ".9440259887086854"  ".8606822927965568"  ".8447592453595382"  "-1.698355737774726" ".5127837598584295"  ".009845829389005"  
"01apr1964" "1.734484676308298"  ".8715861062185633"  "1.679390069354753"  "1.569098013124387"  "1.159399885538991"  ".3693331801199505"  ".4525099186872378"  "-.5149745502293951" "1.087076251817215"  "-.4878775389230119"
"01jul1964" "1.446477024834592"  ".4446847066132618"  "1.282686653509209"  "1.844521635032474"  "-.5393184637666767" ".2322778950027193"  ".5678956526158467"  "-.2666105972877823" "-.553470309218246"  ".2115636157630214" 
"01oct1964" "1.562889776954102"  ".676627587111945"   "1.131892921737746"  "1.906727747972031"  ".3147533656718683"  ".7342085325944484"  ".9888539513331548"  "-.5588034831014443" "1.254048938168931"  "-.1870323913065357"
"01jan1965" "-.7770969052705949" "-1.512988072012822" "1.237467593479263"  "-.577513115129842"  "-.0420592696057363" ".5395446741052972"  ".4673248258159708"  "-.7195095702989585" ".5865187771549574"  "-.1863130301950011"
"01apr1965" "2.131083902041099"  "2.617426787299745"  ".7309426724653341"  ".5018898669430844"  "1.639655162214056"  "2.074808779755221"  "2.468141660842969"  "-.7341192145896416" ".9982615118277749"  ".7074707059182995" 
"01jul1965" ".4522377815342461"  "-.3861478642061674" "1.507098135413732"  ".5019955740155905"  ".8709896854955697"  ".1437179600054622"  "1.206219911443775"  "-.1351237986716344" "1.440077419291179"  ".0099029943509844" 
"01oct1965" "1.37106724902054"   ".7130208792045011"  "1.204024814876736"  "1.269573881784313"  ".8165434055988159"  "1.336796475698952"  "1.187412281521526"  "2.05632284493083"   ".4475461626601492"  "-.3835162829439308"
"01jan1966" "2.989559538698568"  ".9755940543694648"  "1.911106426111434"  "4.343103097214621"  ".6380121136528563"  ".848947890937317"   "1.392360098672"     "1.106695966036429"  ".8469757071329148"  ".5030020009062299" 
"01apr1966" "1.010392879788524"  "1.152529911690924"  ".4960274302606272"  ".186832598733116"   "1.289319920882488"  "1.382549797151781"  "1.44983096526942"   "1.194353831780528"  ".7801054978809909"  ".4961709288873585" 
"01jul1966" "-.8211857268176199" "-1.84379319854661"  ".601560502201531"   ".503121641633439"   "-.910830461575513"  ".7260860775254138"  ".2311688098935802"  "1.428108140431458"  ".9175377526388033"  ".0027627196066633" 
"01oct1966" ".5106353127449059"  ".6656517350270938"  ".0356526806521705"  ".179766877490417"   "-.3985568731896644" "-.3156039778678864" ".0755440531090297"  "2.173199999256295"  ".7071912634993399"  ".002920162991501"  
"01jan1967" "-.5791697797795964" "-.4558543252893984" ".6171421861578836"  "-1.238126008190143" "-1.72427282643924"  ".0198845843913048"  "-.57176557915812"   ".4346523286650065"  ".0496198962768041"  ".3819912846722675" 
"01apr1967" "-.3386850516552029" "-1.099549109043046" ".2316462310028997"  ".7448987096619081"  "-1.361394058758344" "-1.874069947371308" "-1.375557844725085" "3.926357314138268"  ".1115271975070307"  "-.0035481845483056"
"01jul1967" ".8474874681438693"  "1.004043927394374"  ".352743319242739"   ".210891961116713"   ".7004424481280962"  "-.0989744263116114" "-.6885150345524041" "-.8656060132057894" "-.1779062157443193" "-.1892627531632378"
"01oct1967" "-.4353933021056668" "-.7196139345991366" "1.203293342446026"  "-.9778154193568774" ".1136653272159302"  "-.9489007958514641" "-.6555273792031289" ".7560645030600347"  ".8997718998462967"  ".0925326184720688" 
"01jan1968" "-.2848750319926338" "-.3564450751428609" ".0551590824458298"  "-.2105289986939464" ".9495473651720563"  ".6131805300437472"  ".4100271262045093"  "-.2081720201250499" ".8344798876664551"  "-.1829120923509563"
"01apr1968" "2.377398164886389"  "1.62804917321992"   "1.657457968146071"  "1.920649269446877"  ".5972545558494186"  ".8212479092290407"  ".8580470248144195"  ".8145030802227672"  ".160723677526873"   ".8128165713928067" 
"01jul1968" "1.079740584848722"  ".2821693962910181"  "1.605232000885171"  ".8937829965454899"  "-.5650322287390143" "-1.32088652424397"  "-1.178739070306428" "-.5588034831014443" "1.051826140701213"  ".3387707627336444" 
"01oct1968" "1.603033303706275"  "1.070251999025629"  ".998264815363752"   "1.40273801223266"   ".2813449011693839"  ".3740777848710688"  ".1000339391653166"  "-.5734131273921275" ".9577020884451108"  ".1486124887732554" 
"01jan1969" "-.5272002841166726" "-.6443923088454776" ".6243123416037663"  "-.8118660068894953" ".6569976525854296"  ".7126586090114193"  ".753617796958466"   "-.5149745502293951" "1.141599463101798"  "-.4567273567037548"
"01apr1969" ".4610824734080385"  ".2025424060886231"  ".1441311774111799"  ".7267020481666029"  ".8519003487361485"  ".7705209551326886"  ".673700915840778"   "-.4273166844852964" ".9176294007787368"  "-.0996980345353093"
"01jul1969" "-.2350112605413687" "-.5326506502741893" ".9252422328742473"  "-.5297375285797549" "-.1968620366422049" "-.1102662798693819" "-.0371399026584756" "-.9532638789498882" "1.04629082591348"   "-.0120660507931358"
"01oct1969" "-.47225354082037"   "-.5377877348121621" ".0053378901315474"  "-.3079743067825998" ".2275460680295671"  "-.0202342997893399" ".5366314303612599"  "-.1205141543809513" ".5892434617963037"  "-.0936977087241521"
"01jan1970" "-.0032792055477978" "-.5716599444605845" ".9407424907406815"  ".104701003639077"   "-1.512352801123821" "-.7584701720683181" "-.8298496045736499" "-1.523040006286529" ".4840116688228455"  "-.0087888282934416"
"01apr1970" "-.2720469868260188" "-.9710421678446456" ".5927833929733638"  ".3647797811251412"  "-.6855475465188937" "-.713278551584367"  "-.9183033725431918" "-1.201627831891501" ".0336102910615345"  "-.3301642028694415"
"01jul1970" "-.5242973035531585" "-.0456596474901753" "-.4143356944684061" "-.9356064229984965" "-1.431804872683593" "-.6182129337048297" "-.8941855461632832" "-1.712965382065409" "-.8540310860896672" "-.3206456301386363"
"01oct1970" ".1130394350764603"  "-.3166481963767204" ".7056055420840501"  ".1784443672558487"  ".342009018793706"   "-.2607288937203627" "-.3977870366682341" "-.9386542346592049" "-.9662232963340528" ".163547915715044"  
"01jan1971" "-1.620372641016939" "-2.806421990614395" "-.6470521465175489" ".8083289355668566"  "-.4668052812522925" "-2.354356236683905" "-2.288908186865287" "-.4565359730666627" "-.249668639517186"  ".5520361986297447" 
"01apr1971" "1.670423139635876"  "3.057171010330006"  "-.2442254642831571" "-.2567152059191636" ".5047210399082925"  ".1047316214498423"  "-.1669373729709459" "-2.019767912169754" ".3333711798156014"  "-.6295336343876677"
"01jul1971" ".1766471061234129"  ".328580366379263"   ".4305477080731884"  "-.5231170924482551" "1.288192598270015"  ".3573821591135765"  ".0951695994286574"  "-.9094349460778387" ".1991594926212338"  ".0042487470070704" 
"01oct1971" "-.0062275272890239" ".458963855892864"   ".2862249437969455"  "-1.137983533238788" ".356462975897121"   "-.2350413475463347" "-.3548476895206762" "-.4857552616480288" ".3208172671420973"  "-.5329716999562912"
"01jan1972" "1.283207030474974"  ".9944291271265092"  "1.629153428624294"  ".112844980818614"   ".777423165427256"   ".7028015808631489"  ".4261241764055114"  "-1.712965382065409" ".1171145059790657"  ".0870517630456919" 
"01apr1972" ".7767700902324838"  ".3354374728116997"  "1.891200713436481"  "-.3909748807505453" "1.52944011588873"   ".8205781082190974"  ".9726948505182386"  ".2593365971768093"  "1.195143106558721"  ".1618984113998823" 
"01jul1972" "1.646850410716824"  ".4101759947300904"  ".8707110933541035"  "2.947821535784159"  ".3590930272541478"  ".3104159241267031"  ".2267761205245234"  ".3469944629209079"  ".7094722374663045"  ".0085309203833936" 
"01oct1972" "1.107098376934679"  ".428906811726049"   "1.169968774714156"  "1.102529243346497"  ".0862688121727292"  ".3491821503505885"  ".1457181297067658"  "-2.735640482413226" ".3892030318764719"  "-.5956212307372077"
"01jan1973" "2.303708941197178"  "1.329887898843835"  "2.112543555506042"  "1.823244162087189"  "1.579431099813009"  "2.083859178533785"  "1.732474618836299"  "-.0912948657995851" "1.403416287723386"  "1.58993907073789"  
"01apr1973" "1.53101542837176"   "1.678008824561743"  "1.02778367370448"   ".0229406917459784"  "1.371310240339713"  "1.381416525345491"  "1.450809448449703"  ".5369198386997882"  "1.186470222790553"  "-.674284269067897" 
"01jul1973" "-1.277001348923534" "-.8466701601345611" ".0950556388602271"  "-2.040821312239445" "-.085368639194806"  "1.092025439211075"  "1.226361806161377"  "-1.157798899019451" ".2098403366955086"  "1.031406574006964" 
"01oct1973" "-.6766793245606977" "-.7415853243807145" "-.3224879317244568" "-.1685016711512946" "-.5759462618288635" "-.0447442022278105" ".182506062725836"   "-2.195083643657952" "-.4328741765887915" "-.9567068655553999"
"01jan1974" "-1.661023845133604" "-1.241584833852614" "-.9636949670514554" "-1.397441208155819" "-.8392736192470789" ".0903083792960127"  "-.2021801217358961" "-.1059045100902682" ".7541151064454259"  ".6501686904352436" 
"01apr1974" "-2.569592588775919" "-1.239026180608115" "-2.165118154907983" "-2.753448016116959" "-1.374649935199472" "-.4308659939187616" "-.6214844120597705" "1.223573120361894"  "-.8165074396295986" ".9083992969347736" 
"01jul1974" "-.6644845282829209" "-.2507623629004352" ".4167222245443467"  "-1.788179136868962" "-1.031721724343024" "-.5267158040195421" "-.6553749974370949" "-.4419263287759795" ".218774528250172"   "1.064906326537202" 
"01oct1974" "-.559644869010042"  "-.2487190006498987" "-.6080650292517027" "-.5428510623056731" "-1.23484671063808"  "-.3038710377590775" "-1.075596335520697" ".1424594428513445"  ".0242134395579938"  "-.147925126466206" 
"01jan1975" "-3.438485104267311" "-3.895517472196093" ".4662871678029365"  "-3.42641862570866"  "-3.079730577865029" "-1.913883678803197" "-1.881793374747921" ".3469944629209079"  "-.8119415197265335" "-1.317534991653828"
"01apr1975" ".0560660247270305"  ".2551710630258799"  ".2767376034572848"  "-.5980737827738064" "-3.245375704986546" "-3.099794343899408" "-3.462417069001491" "-.8363867246244232" "-2.043020519386357" "-1.322677650130021"
"01jul1975" "1.261786605897996"  ".2012116200195923"  ".8365990298976342"  "2.257661064189784"  "-1.014745136578465" "-1.207446393983069" "-1.768005708778405" "-.8071674360430571" "-.6489150084463926" "-.4485852148798758"
"01oct1975" ".9282735592420885"  "1.632092038517535"  "-.7237562008349547" ".5294154789421791"  ".6788605719482625"  "-.0494150897670128" "-.0488707292775101" "1.325840630396675"  "1.159314876273739"  ".5304822528047869" 
"01jan1976" ".410925863931661"   ".5460136144076979"  "1.356936226627442"  "-1.189224706004883" ".4817820039283792"  ".0357309035571498"  ".0132287507480207"  "-.0474659329275358" ".2959375838937844"  ".1997759802784533" 
"01apr1976" "1.789012314248477"  "1.247653409270552"  ".7701128752680693"  "2.016660397233809"  "1.447507251290077"  "-.0260520797032605" ".4726903602541866"  "-.8071674360430571" "1.723983433107468"  ".3862055121369274" 
"01jul1976" ".1781129182920684"  "-.2786472943221697" "-.3084927620379828" "1.328835186486286"  ".3264138483658793"  ".1736312488602135"  ".0787406637737185"  "-.4419263287759795" ".8774233770177078"  "-.2550004762766421"
"01oct1976" ".3772494396542608"  "-.0479463001314934" ".8662612015822179"  ".2427014506601045"  ".0823693167748556"  ".4605426245670524"  ".4941195024734969"  "-.1497334429623175" ".1922189514120525"  ".9374208911870856" 
"01jan1977" ".7433811881985081"  ".0983993067739098"  "1.213658069790677"  ".6456312165139634"  "1.745153938003718"  ".4901409350874407"  ".3714440489460375"  "-.1059045100902682" ".0095021831818363"  "-.6941627432223206"
"01apr1977" ".5656950418357291"  ".8283949453597093"  ".914090364955625"   "-.9387556318845575" "1.154202524273226"  "1.685458569449965"  "1.190952951400212"  ".1716787314327107"  "1.044648546249621"  ".3023085235490867" 
"01jul1977" "-.3898342487475266" ".3585867896362712"  "-.9499821719277439" "-.859684628683116"  "1.94868628397752"   ".4003644633359048"  ".7715859730042768"  ".5223101944091051"  "1.313064970298759"  "-.5494290839001158"
"01oct1977" ".2163788627599916"  ".0579986470869157"  ".5600417201379527"  "-.1433342187743993" ".1461079211411683"  ".3775206461461043"  ".4016333789366486"  ".3616041072115909"  "1.258935484584246"  ".7781081487126212" 
"01jan1978" "1.051087923229957"  ".2702198453418733"  ".3024906424603198"  "2.177847361704409"  ".3522365645949304"  "1.132235060525176"  ".9050107348011314"  "-.6318517045548598" "1.522962085688418"  "-.8370348630842486"
"01apr1978" "-.3603944008469778" "-1.368915459630079" "1.619634149176558"  "-.2406078235347345" "-.0325077351943085" ".0189497317913096"  "-.1410818137378572" "-.1935623758343668" "1.076003193364377"  "-.8770610772120535"
"01jul1978" "1.994452586564669"  "2.029065865419627"  "1.493190675254634"  ".2421106102103471"  "2.677499039981866"  "2.149827523096719"  "3.102361465366324"  ".9313802345482319"  "1.607117858307983"  "2.231947075388632" 
"01oct1978" "-.5742037512978059" "-.9068000128614087" "-.1890450533822254" ".2485174118483756"  ".81918751196242"    ".1980122028522099"  "1.072090841706122"  ".1424594428513445"  "1.077282315624789"  "-1.517410660881206"
"01jan1979" ".0082990718208445"  "-.1681939652244558" "-.6073324403980389" ".9767019984133855"  ".5841425435978528"  ".4483184447885399"  ".994419957599628"   ".2593365971768093"  ".7398414043699102"  ".5784172817392919" 
"01apr1979" "-.4264559898412105" "-.844940410121049"  ".5128568837998314"  "-.2246224227064483" "-.0185981641920819" ".7559261720306359"  ".5493865495485033"  "-.3250491744505147" ".6021572210001737"  "-.1148194976433514"
"01jul1979" "-1.30708522382794"  "-1.223489805063096" "-.4116313019725195" "-1.117990189600554" "-.7124749089488118" "-.9694497373400461" "-.6181518073800458" ".3616041072115909"  "-.5155554868413377" ".5590838024749157" 
"01oct1979" ".2576835507497248"  ".5058599426656887"  "-.9382794440484308" ".8218103477449816"  ".3816088234371109"  ".2889721980378507"  ".8627471266088126"  "-.1059045100902682" "-1.135745862631755" "-.6183935917493396"
"01jan1980" "-.8324074994883689" "-1.337397896369273" ".0532806322915055"  "-.083061147371501"  "-.8821590677511137" "-1.007399801233128" "-.4365655168989462" "-.1789527315436837" ".6392563974414134"  "1.311364712442127" 
"01apr1980" "-1.42534357567006"  "-.987253345265836"  "-1.39917939432807"  "-.8916236556492501" "-1.07013325557904"  "-.2091086172596979" "-.037431170760367"  "1.384279207559408"  "-.1453281309984123" ".9781453313311493" 
"01jul1980" "-4.657974857582793" "-4.364649786811445" "-3.138536278701797" "-2.774413417053343" "-4.495453032323069" "-3.043776270985513" "-2.900733008241692" ".9459898788389149"  "-1.94999924279101"  "-1.109604869661429"
"01oct1980" ".4433066902882825"  "1.051562784679944"  ".7518489179100984"  "-1.188831976709037" ".118880517460229"   "-.2164751983685571" "-.2486913371511683" "-.8948253017871557" "-1.074395006560139" "-.6428196697394695"
"01jan1981" ".8234674590138357"  ".5923410920163286"  "1.566894747669301"  "-.2944443236022168" "1.300291739167926"  ".0200933596691783"  ".4473166161982164"  ".0548015771072459"  "-.0772395022088895" ".7527876517506704" 
"01apr1981" "-.7022921580709627" ".5230786083406452"  "-2.992492524153477" ".3939501176500299"  "-.0196264544110912" ".435629420486675"   ".4194349092956191"  ".9459898788389149"  ".1431054920374995"  "-1.177694442966256"
"01jul1981" "-1.197697084752666" "-1.805391093560176" "-.2613700227210714" "-.224485637248193"  "-.0785615608123061" "-.18826901181601"   ".4216888215305774"  "1.325840630396675"  "-.0142435241326911" ".1997156394563981" 
"01oct1981" "-.5098691944679384" ".5861214071799972"  "-1.713327905471572" "-.5346286430459563" "-.4300193707735835" ".3089651062707783"  ".5586267167336916"  "-.4711456173573458" "-.6091179510140569" "-.0021147525558502"
"01jan1982" "-2.248065097353864" "-3.008664602207005" "-1.300923716176038" "-.2989424925959592" "-.5965395604414483" "-.8956190825151222" ".5622122803603822"  ".8437223688041334"  "-1.46654405347657"  "-.3916211789602886"
"01apr1982" "-.0974175576656333" ".6349895923030767"  "-.6299796919793881" "-.5795861768007562" "-1.771270894801755" "-1.262769174384168" "-1.60900227695349"  "-.1935623758343668" "-1.319542103153116" "-.2858201448650307"
"01jul1982" "-.7481796823212296" "-.3221311708554751" "-.5508415978831473" "-1.088799720463149" "-1.954300899834268" "-1.958273669887708" "-2.129809652019233" ".0109726442351966"  "-.5751232968697603" ".1533824441781703" 
"01oct1982" "-.1969757132320966" "-.1697314715436273" "-.0810959251682326" "-.2391627268299302" "-1.586674952745303" "-1.154541580423149" "-2.007514483821517" ".7414548587693517"  "-.8270844673672986" "-.1367727619468594"
"01jan1983" "1.420813768752577"  "1.089052207181701"  "1.588949158190317"  ".6039977894947873"  "-.4236002359842029" "-1.133311368422335" "-1.426643249152767" "1.194353831780528"  "-1.198179307332696" "-.4693108662474265"
"01apr1983" ".2810884072434068"  "-.0996750844081976" "1.133796486876347"  "-.3915984396721146" ".6007392619845878"  "-.7149333663406687" "-1.312528052687984" ".7122355701879854"  ".0733775337841033"  ".7271861694636627" 
"01jul1983" "1.843757869197683"  "1.976310853898541"  "1.198622811908769"  ".8170022400961375"  "1.298255994810679"  "1.230164582715647"  ".1088688959481162"  ".8875513016761827"  ".8448609133310636"  ".3364516034806304" 
"01oct1983" "1.482607160920874"  ".854458266526483"   "1.440378609498222"  "1.303416949138213"  "2.13077387364403"   "1.429757173486287"  "1.459445793582445"  "1.325840630396675"  "1.640138612052191"  ".2343754563478202" 
"01jan1984" "1.185335587048806"  "1.214761959626268"  ".4860301306858448"  ".8815955607673845"  "2.117433318571905"  "2.445150725572"     "2.319282170973286"  "-2.034377556460438" "1.300404126652442"  "-1.53469623461745" 
end

The actual dataset is much larger, namely I have 250 series, and the group number is indicated above. There are in total 14 groups, and I would like to first get the data in a long format (otherwise wide if long is not possible), and then create a group variable to assign each series to their respective group. I am aware of the reshape command to adjust this data to get the proper format, but with this kind of complicated dataset I am a bit confused where to begin.

If anyone has a clue on how to do this, I would really appreciate it.

Kind regards,

Ryan

[Mitch Downey]

Hi Ryan,

Yes, it's a complicated dataset. This code works. It's a bit hackey and complicated, but works fine for this example. I think it should generalize fine if and only if you don't have underscores in any of your variable names. If you do, just use some random character set like XYZABCDEF in place of _ in the code.

-Mitch

list in 1/3
foreach v of varlist C-GZ {
  replace `v' = `v' + "_" + `v'[_n-1] if _n == 2
}
list in 1/3
drop if _n == 1
outsheet using temp.csv, replace nonames
insheet using temp.csv, clear names

local stublist = " pcecc96_ "        // There's probably an elegant way to avoid this, but you have to initialize the list with one variable name

foreach v of varlist * {
  local stub = substr("`v'",1,strpos("`v'","_"))
  local listlength = wordcount("`stublist'")
  local testvar = 1
  foreach n of numlist 1/`listlength' {
    if word("`stublist'",`n') == "`stub'" {
      local testvar = 0
    }
  }
  if `testvar' == 1 {
    local stublist = "`stublist'" + " `stub' " 
  }
}

reshape long `stublist', i(series) j(group)

foreach v of varlist * {
  local newname = subinstr("`v'","_","",.)
  rename `v' `newname'
}
rename series date

[Clyde Schechter]

It appears that your group variable consists of groups of series. A given group contains, potentially, several series, but each series belongs to only one group. Is that true in your data as a whole, or is it just a fluke of your example? If this is what your data actually are like as a whole, then I don't understand what you want: the data are already in long format. There would be no sensible way to create a group variable, as it is an attribute of the series, not of the observation (which is characterized by quarterly dates). The work facing you would be to properly rename the variables, and convert the string variables into quarterly dates and numbers. So that would look like this:

ds A, not
foreach v of varlist `r(varlist)' {
    char `v'[group] `=`v'[1]
    rename `v' `=`v'[2]'
}
drop in 1/2
gen int qdate = qofd(daily(A, "DMY")), before(A)
format qdate %tq
assert missing(A) == missing(qdate)
drop A
ds qdate, not
destring `r(varlist)', replace

The original group designation for each series has been stored in the group characteristic of each series.

Now, if your example has not really represented your data, and in fact each (or many) of the series recurs for each (or many) of the 14 groups, then it would be different, and it truly would involve reshaping:

gen int qdate = qofd(daily(A, "DMY")) in 3/l, before(A)
format qdate %tq
assert missing(A) == missing(qdate) in 3/l
drop A

ds qdate, not
local stubs
foreach v of varlist `r(varlist)' {
    local vname = `v'[2] + "_" + `v'[1]
    local stubs `stubs' `=`v'[2]'
    rename `v' `vname'
}
drop in 1/2
ds qdate, not
destring `r(varlist)', replace
local stubs: list uniq stubs
reshape long `stubs', i(qdate) j(group) string
replace group = substr(group, 2, .)
destring group, replace

Added: Crossed with #2, which presents a largely similar, though different in detail, approach to my second scenario and does not consider the first one.

[Ryan Marapin]

Dear Clyde,

First of all thank you for taking the time to answer this question, I appreciate this. Sorry for the confusion, here is a new example of my dataset:

* Example generated by -dataex-. To install: ssc install dataex
clear
input str9 A str18(B C D X Y Z AK AL AM)
"group"     "1"                  "1"                  "1"                  "2"                  "2"                  "2"                  "3"                  "3"                  "3"                
"series"    "GDPC1"              "PCECC96"            "PCDGx"              "IPFINAL"            "IPCONGD"            "IPMAT"              "USPRIV"             "MANEMP"             "SRVPRD"            
"01oct1959" "-.8223981596001021" ".3650269129167977"  ".216028379993192"   ".5164586799521738"  ".4669263794759714"  "-5.473431289722265" "-.8710791702661019" "-.5977236356222472" "-.1896159530114201"
"01jan1960" "-.561912165990163"  "-1.038272296385466" "-2.1248980673514"   "-1.262337723933731" "-1.145523372720032" ".7946468296534323"  "-.3731959171480947" "-.0197528235492228" ".4790178102485519"
"01apr1960" "1.80279703884361"   ".2330519053439989"  ".6462049897425028"  "2.20155760075351"   "2.515808462937025"  "4.082689673469607"  "1.471869992598859"  "2.239490886003505"  "1.161471148358175"
"01jul1960" "-1.569258492265201" ".6894363614418856"  ".3311574486157206"  "-.4595035172364882" ".0340225235151178"  "-2.695599651578648" "-.5337584662697544" "-.811144920092954"  ".4960302332017645"
"01oct1960" "-.3136409719859831" "-1.815167574724039" "-.7182466608899543" "-1.410977789871182" "-1.26564962017662"  "-1.319746207388967" "-1.698630864444197" "-1.068032672244793" "-1.646363629073115"
"01jan1961" "-2.485586804542208" "-1.012717500919827" "-1.31699927459115"  "-1.280950703438864" "-.8652131001342129" "-2.334435741091222" "-2.08802505558213"  "-1.518297429742752" "-1.215309737658085"
"01apr1961" "-.0882738941519432" "-1.265030523659988" "-2.385659931921413" "-1.520771166880837" "-1.519714838393087" "-1.288580235098284" "-2.022680311966717" "-1.569262012748853" "-1.439174208884885"
"01jul1961" "1.1448204188911"    "1.028181956482924"  ".2635462626236992"  "1.423211318610236"  "2.402321515147232"  "2.422051319008141"  "-.3843683690929127" ".6191043194578191"  "-.7447778992164527"
"01oct1961" "1.411110830318517"  "-.4741309717936375" ".386784411896169"   ".9076962132731495"  "1.077616357683101"  "1.918831921713351"  ".6322240369817158"  ".9966294761815471"  ".7200854873621609"
"01jan1962" "1.46075396774132"   "1.806877163511684"  "1.184793236978319"  "2.012975168708311"  "1.900554655211966"  "1.528776199812922"  ".6686275824952422"  "1.07532188511387"   ".2581863662540683"
"01apr1962" "1.248116832146391"  ".3868891877851496"  ".2951560576894264"  ".6539905346539944"  ".119930940838851"   ".4974778289090573"  ".5197935381849891"  ".9945157005344126"  ".4052157371564657"
"01jul1962" ".1885573693874193"  ".6415112640529802"  ".4690597982810318"  "1.127815467491937"  ".9201052615307053"  "-.5658130163804718" ".7761063227161175"  ".7893244779891736"  ".8507110706215719"
"01oct1962" ".5807787765656831"  ".006811979715034"   "-.1923909169116167" ".5119226861829268"  ".2935775785368553"  "-.028375502546226"  "-.253937299388836"  ".1733699124028184"  "-.0721039789450881"
"01jan1963" "-.5085992379083352" ".918796140849536"   "1.038266785708293"  ".0467366183748433"  "-.0794272595787167" ".1692703990984082"  "-.2933802504770557" ".096525942492976"   "-.0632041002072682"
"01apr1963" ".4156305360073915"  "-.1516759755249011" ".2355007054356036"  "1.260191255665909"  "1.603407102070793"  ".540353830788342"   "-.4203015033871569" ".1348840914081418"  "-.0318340693702387"
"01jul1963" ".4524649620249028"  ".2134739499711086"  ".2871131404648894"  ".25141689638957"    ".6740265080435996"  "1.584312910266632"  ".4578794754149625"  ".4867847324201772"  ".3793304817094532"
"01oct1963" "1.744454928822105"  ".8317360486024667"  "-.0199021398544998" ".3410518210940962"  ".2347206439049873"  "-.2670172658664872" ".0505040015808527"  ".2584728457823877"  ".1567431499786041"
"01jan1964" "-.111831784155457"  ".0478190578910472"  ".0960184847336646"  ".6416292654468333"  ".7011061981779394"  ".5374707833282639"  ".1776324429477705"  ".3341022668551226"  ".8081187380318079"
"01apr1964" "1.636765677148209"  "1.734484676308298"  ".8715861062185633"  ".3204895457841441"  ".4665075149358031"  ".4470847961387091"  ".3602238593229162"  ".3125095196043419"  "1.107008412884303"
"01jul1964" ".4121287620134346"  "1.446477024834592"  ".4446847066132618"  "1.1677831140327"    "1.561432485213538"  ".8342302270501043"  ".2680750065780814"  ".4685541841885422"  ".2424660153561845"
"01oct1964" ".9825064993879449"  "1.562889776954102"  ".676627587111945"   ".2952122694922396"  ".4128518978012652"  ".993393592550982"   ".9563659493127015"  ".9593206745456679"  "1.011996873468028"
"01jan1965" "-.532888768308146"  "-.7770969052705949" "-1.512988072012822" ".3548265318680091"  "-.1105171695819052" ".7486509431619455"  ".4773986829439928"  ".4183144228435438"  "1.02101654109992"  
"01apr1965" "2.008462897735706"  "2.131083902041099"  "2.617426787299745"  "2.537586006420369"  "3.00902057101774"   "1.432305565606911"  "1.105400855198736"  "1.542178144130728"  "1.004153102743358"
"01jul1965" ".6226789644170452"  ".4522377815342461"  "-.3861478642061674" "1.099504856581315"  ".3242676272586804"  "1.016923205596941"  "1.138257146192178"  "1.092479798301676"  "1.651377151364982"
"01oct1965" "1.774114523088277"  "1.37106724902054"   ".7130208792045011"  ".9427959381060907"  ".2515722621925856"  ".7244805952796438"  "1.117584901014471"  "1.427405631596863"  "1.459605470002228"
"01jan1966" "1.870503832652116"  "2.989559538698568"  ".9755940543694648"  "1.54384655932907"   "1.161016868087236"  ".1333044598362751"  "1.254297264490474"  "1.266651849354159"  "1.539718008904925"
"01apr1966" "2.025698377922191"  "1.010392879788524"  "1.152529911690924"  "1.509621069930838"  ".7692960911592891"  "1.249298901321402"  "1.331831990822084"  "1.735579603709326"  "1.646949070022774"
"01jul1966" "-.4930848699617391" "-.8211857268176199" "-1.84379319854661"  "1.093672635706918"  ".4363939357577618"  ".7682280695181556"  "1.464678684230938"  "1.857680002265755"  "1.995000218579468"
"01oct1966" ".1198851573658766"  ".5106353127449059"  ".6656517350270938"  ".6461158296669397"  "-.3694278479492513" ".5792360755719488"  ".9605838296921742"  "1.171643699431191"  "1.548349840762401"
"01jan1967" ".0877744569305525"  "-.5791697797795964" "-.4558543252893984" ".778639567246231"   ".9688190546612273"  "-.1372471572061918" ".383862607256282"   ".7511883962419427"  "1.385575503071429"
"01apr1967" ".1666834744440063"  "-.3386850516552029" "-1.099549109043046" "-.0870013729796224" "-.5366174070454388" "-1.638371532448894" ".1108807443093101"  ".1000307718019594"  "1.143022262789211"
"01jul1967" "-.8345076795758143" ".8474874681438693"  "1.004043927394374"  "-.1396006768750944" "-.175873729307018"  "-1.138647310645758" "-.6137519981608356" "-.6407679397612239" ".6132683517855088"
"01oct1967" ".2398981887856186"  "-.4353933021056668" "-.7196139345991366" "-.5672579727111788" "-.2853738046957144" ".2514506619912872"  ".3357023942450564"  ".0082769381074943"  "1.076515367610516"
"01jan1968" ".0074364970861345"  "-.2848750319926338" "-.3564450751428609" "1.564337838017924"  "2.453625842780276"  "1.084143617616065"  ".6031386634222528"  ".6188426008825076"  ".9622458585330522"
"01apr1968" "1.554531034252141"  "2.377398164886389"  "1.62804917321992"   ".424852216013014"   ".3640888957267546"  ".5369847480594759"  ".3807073103228038"  ".6551926356567964"  ".7338543182205797"
"01jul1968" "1.112814450505291"  "1.079740584848722"  ".2821693962910181"  "-.0042945752837207" ".3588119659436307"  ".7046766373904779"  ".7418192433991091"  ".7121404792100634"  ".8595439549111918"
"01oct1968" ".032810426474949"   "1.603033303706275"  "1.070251999025629"  ".2389785562885361"  ".5914474266691886"  "-.0761818974070856" ".7184313478271648"  ".4368649633428986"  "1.315464196325725"
"01jan1969" "-.4302425119839238" "-.5272002841166726" "-.6443923088454776" ".5830278751838249"  "1.116332986543495"  ".3329859505508665"  "1.006127198149022"  ".5264550350374205"  "1.37104685233077"  
"01apr1969" ".9858786711714366"  ".4610824734080385"  ".2025424060886231"  ".5410492314684284"  ".6641789755556187"  ".7387358110470471"  ".986404997103757"   ".7905929200656672"  "1.155193391083467"
"01jul1969" "-.5388325190152004" "-.2350112605413687" "-.5326506502741893" "-.6811157709493809" "-1.025772755517903" ".272439177771396"   ".9540151250888288"  ".5398331223884067"  "1.274160269570253"
"01oct1969" "-.1058715949049002" "-.47225354082037"   "-.5377877348121621" ".4135536781822071"  "1.046610897288119"  ".4498485897314137"  ".6082265650825787"  ".4687780390297291"  ".7422755485128893"
"01jan1970" "-1.505840556321441" "-.0032792055477978" "-.5716599444605845" "-1.374955566658383" "-1.171701104995608" "-.3394796958096653" "-.2142378697917025" "-.6705124840806901" ".6581068913075736"
"01apr1970" "-1.090587903849204" "-.2720469868260188" "-.9710421678446456" "-1.820075201565137" "-1.334510049814613" "-1.891144656514812" "-.5009220934776245" "-.605337243838205"  ".2794176682279384"
"01jul1970" "-.7361906092987534" "-.5242973035531585" "-.0456596474901753" "-.5078448531284984" ".4581330074816028"  "-.9783797440278649" "-1.498807680301817" "-1.529115154676207" "-.2874376469907878"
"01oct1970" ".210207826081956"   ".1130394350764603"  "-.3166481963767204" "-1.44179288311444"  "-.8836275074571528" "-.0021119012186352" "-1.334853052945177" "-1.287930865715752" "-.9341646158878365"
"01jan1971" "-2.224055880749505" "-1.620372641016939" "-2.806421990614395" "-2.199152862090856" "-1.193869893725983" "-1.438427619074976" "-2.143786966346394" "-3.070243846525367" "-.4190465839236563"
"01apr1971" "2.360902325807622"  "1.670423139635876"  "3.057171010330006"  ".9142061037302394"  "2.736924401081062"  ".7894196334395512"  "-.1270939144104377" ".3232777877391231"  "-.1985798012697597"
"01jul1971" "-.2507074088381729" ".1766471061234129"  ".328580366379263"   "-.2128361498588056" ".6061988733516271"  ".4024530515739445"  "-.0097122325588947" "-.1641326484065955" ".0170160198472743"
"01oct1971" ".0909363661072287"  "-.0062275272890239" ".458963855892864"   ".5331250288909077"  ".8364499951151119"  "-1.027399360751526" "-.1780974386255376" "-.1316840131181257" "-.1489282770590319"
"01jan1972" "-.6218681610712521" "1.283207030474974"  ".9944291271265092"  "1.103154038740327"  "1.386303881313165"  ".7032747645100372"  ".1839959230503055"  ".2456657561412073"  ".3510193865252542"
"01apr1972" "1.314464818756735"  ".7767700902324838"  ".3354374728116997"  "1.686122009892167"  "1.56608555493787"   "2.284056266997474"  "1.137372097545448"  "1.104779649172485"  "1.347811770441307"
"01jul1972" "1.829602368145429"  "1.646850410716824"  ".4101759947300904"  ".6896546235727649"  ".4524668747308068"  ".7062384675965233"  "1.167340164105435"  "1.199941875858933"  "1.15788162484112"  
"01oct1972" ".2377926892531946"  "1.107098376934679"  ".428906811726049"   ".6677466090008936"  ".8039744925014585"  ".053441767463545"   ".4735474005031615"  ".6145865535729049"  ".7159249939441698"
"01jan1973" "1.117197720356583"  "2.303708941197178"  "1.329887898843835"  "2.10390402702979"   "1.824019958413872"  "1.410533629089275"  "1.481004525461084"  "1.718798966931193"  "1.40734657530358"  
"01apr1973" "2.074033621465553"  "1.53101542837176"   "1.678008824561743"  "1.343223238965412"  ".734571394079734"   "1.241651128125494"  "1.753129614730145"  "1.913122015119566"  "1.317564643158779"
"01jul1973" ".4120646084776839"  "-1.277001348923534" "-.8466701601345611" "-.0036902538809489" "-.5686973279468028" ".1498952852251013"  ".8075814633905796"  ".9142855810646043"  ".5817451301946696"
"01oct1973" "-1.55271543284473"  "-.6766793245606977" "-.7415853243807145" ".0113604225299749"  "-.8286919536809572" ".1576937250977659"  ".3006579068424715"  ".2916763245256961"  ".0351248383316524"
"01jan1974" ".2436369324857551"  "-1.661023845133604" "-1.241584833852614" ".6498867375962222"  ".1928856937794307"  ".4584540781221139"  ".8133370659624051"  ".8059575634137744"  "1.222652320289999"
"01apr1974" "-1.962975894911905" "-2.569592588775919" "-1.239026180608115" "-1.587110302065288" "-2.314622738485986" "-.5591491761406654" "-.0228652783728707" "-.0537385801507027" ".3303120303651009"
"01jul1974" "-.6195577226991309" "-.6644845282829209" "-.2507623629004352" "-.1872199424074464" "-.0633616261763773" "-.3855919552796407" "-.1970588610957462" "-.1416214645722383" ".2710872139759023"
"01oct1974" "-2.068101386365702" "-.559644869010042"  "-.2487190006498987" "-.1436878602757629" "-.2804984442081077" "-.642834719374213"  "-.7518168172029508" "-.5058989096204273" ".2645194535883559"
"01jan1975" "-1.383831232650698" "-3.438485104267311" "-3.895517472196093" "-2.305105018798505" "-3.044079174280717" "-2.743587585805639" "-2.281512511417233" "-2.506830622285166" "-.4675320682243309"
"01apr1975" "-2.403861247556762" ".0560660247270305"  ".2551710630258799"  "-5.336096976545016" "-5.135741701158521" "-3.619072667390395" "-4.357034391505324" "-4.910168741994094" "-1.780744891254918"
"01jul1975" "-.040386253538852"  "1.261786605897996"  ".2012116200195923"  "-.0356088661596103" "1.5581516381615"    "-1.516269106294578" "-2.057583548254923" "-1.701874230660267" "-.9901555578156115"
"01oct1975" "1.16183320954882"   ".9282735592420885"  "1.632092038517535"  "1.687073610502896"  "2.999181832075681"  ".6919919041894574"  ".5204450357136272"  ".4487925454925327"  ".5994125628194096"
"01jan1976" ".7229453307602188"  ".410925863931661"   ".5460136144076979"  ".7634567921950708"  "1.025461703045524"  ".9517282972966363"  ".9300436262259764"  "1.382652006913704"  ".2250318221269887"
"01apr1976" "1.804543471406726"  "1.789012314248477"  "1.247653409270552"  "1.220007766209879"  "1.289980151618701"  "1.457761234816308"  "1.823257830542517"  "1.792049098732334"  "1.399410794705973"
"01jul1976" "-.0171829741457432" ".1781129182920684"  "-.2786472943221697" ".1241776156835171"  ".1374139428240296"  ".4515425888326923"  ".6696865497158497"  ".8151045268802554"  ".4407461948745565"
"01oct1976" "-.2414728908563904" ".3772494396542608"  "-.0479463001314934" ".267518041119184"   ".247615033973248"   ".3135374802475548"  ".228397937896225"   ".4301424715078562"  ".0745045226851586"
"01jan1977" "-.0279037363710222" ".7433811881985081"  ".0983993067739098"  "1.297830596290703"  "1.401194892561662"  ".2679481124771668"  ".3314459663119015"  ".3510341784087355"  ".2015596049138886"
"01apr1977" ".5240879357434508"  ".5656950418357291"  ".8283949453597093"  "1.345436097130412"  "1.042740210104177"  ".5790489043033272"  "1.359344593025917"  "1.145077609825004"  ".7730123091890186"
"01jul1977" "1.440405741921039"  "-.3898342487475266" ".3585867896362712"  "1.378825195008506"  ".7097992965094305"  "1.21378781644796"   "1.729590613896483"  "1.293943227120418"  "1.345216579495568"
"01oct1977" "1.272994114667969"  ".2163788627599916"  ".0579986470869157"  ".6061271192771271"  ".2532965780699745"  "-.0212445383886281" "1.323362666795473"  ".9682448314307274"  "2.015946835601662"
"01jan1978" "-.9065856865106182" "1.051087923229957"  ".2702198453418733"  ".0244460823015055"  ".6594439845703094"  "-.0309854360481268" "1.240615304446573"  ".7832303609151381"  "1.903841504424795"
"01apr1978" "-.5203774256724816" "-.3603944008469778" "-1.368915459630079" "-.0568133435544016" "-.5460126898273679" "-.8421619538354819" ".9711124063067927"  "1.078878437717888"  "1.572280813840822"
"01jul1978" "3.717521382266249"  "1.994452586564669"  "2.029065865419627"  "2.077818550133834"  "1.365629808332268"  "1.886077226423514"  "2.510012050226043"  "1.040808420238628"  "2.334276519324837"
"01oct1978" ".3116727207332497"  "-.5742037512978059" "-.9068000128614087" ".2483351991067582"  "-.6865816213906712" ".0797292839633688"  "1.241377022305377"  ".8392578567178188"  "1.118548492436664"
"01jan1979" ".7201022605358369"  ".0082990718208445"  "-.1681939652244558" ".7591324741510891"  "-.1970934590659337" ".5743585176304683"  "1.300044192419472"  "1.177451919339933"  "1.036884668333553"
"01apr1979" "-.6900588360833861" "-.4264559898412105" "-.844940410121049"  ".2365598891511857"  "-.6373850764070813" "-.3553516215008249" ".8954000135745985"  ".8711137264462616"  ".9351868797182298"
"01jul1979" "-.7788372127951164" "-1.30708522382794"  "-1.223489805063096" "-.947850755575068"  "-1.516113094768355" "-.2868157634378658" ".6246039310626085"  ".4750042754481739"  ".2904258607686696"
"01oct1979" "-.0060650800156641" ".2576835507497248"  ".5058599426656887"  "-.3261285855335453" "-1.38494622721359"  "-.6313536221943844" ".0448466262190429"  "-.1301016830847165" ".3986039247423862"
"01jan1980" "-.6044669480603616" "-.8324074994883689" "-1.337397896369273" "-.0438873655547308" "-.2630216825461145" "-.2316948558150187" "-.0403025121237293" "-.5246577489765402" ".1942565902215382"
"01apr1980" "-.5260490380507301" "-1.42534357567006"  "-.987253345265836"  ".148471899554114"   "-.7117749015525289" "-.1205172899991189" "-.1876835181693317" "-.3471396357571267" ".1993596046196885"
"01jul1980" "-3.449953052657735" "-4.657974857582793" "-4.364649786811445" "-2.544692173294344" "-3.109206562616639" "-2.725227366111575" "-2.215837885269128" "-2.328545496601495" "-.6178211145846844"
"01oct1980" "-1.054373828042152" ".4433066902882825"  "1.051562784679944"  "-.7150162189760938" "-.7764854859650944" "-1.658912778743271" "-1.371982696965763" "-1.620695374124819" "-1.410901417894231"
"01jan1981" "1.345582137081526"  ".8234674590138357"  ".5923410920163286"  "1.558261642107388"  "1.001982214794575"  "1.85877145199951"   ".7939956786568493"  "1.061117641945696"  ".1201110228892378"
"01apr1981" "1.458753759175666"  "-.7022921580709627" ".5230786083406452"  "-.2962387207613724" "-.3393499514636585" "-.2044252610107229" ".255526541919099"   ".3171050125146783"  "-.134166231517033"
"01jul1981" "-1.816971507102129" "-1.197697084752666" "-1.805391093560176" ".2536025509266874"  ".0328969796690201"  "-.4274104488055866" ".0859570016701066"  ".6589667165951514"  "-.6588849399931201"
"01oct1981" ".5434223943035914"  "-.5098691944679384" ".5861214071799972"  "-.0708343243547667" "-.1425273861912506" ".3650185325566671"  "-.0710958569252571" ".0400953780451027"  "-.9074414431546456"
"01jan1982" "-2.246141354568079" "-2.248065097353864" "-3.008664602207005" "-.6876613485272993" "-.6363734716370943" "-1.974801037276585" "-1.505647776798215" "-1.607645501080034" "-1.424513572938376"
"01apr1982" "-2.819945183021569" "-.0974175576656333" ".6349895923030767"  "-1.543544387349028" "-1.077352109695092" "-1.564145008822724" "-1.969534886883729" "-2.080406398705917" "-1.702292243465333"
"01jul1982" "-.3538168074006757" "-.7481796823212296" "-.3221311708554751" "-.664302298168191"  ".1710325421936461"  "-1.364984890732794" "-1.723985694668586" "-1.914277700495513" "-1.435755034694163"
"01oct1982" "-1.376669660792749" "-.1969757132320966" "-.1697314715436273" "-1.095022314606156" ".0345860980797172"  "-1.342265262136346" "-2.008833995687508" "-1.968130299806831" "-1.878700179103738"
"01jan1983" "-.8605220127482057" "1.420813768752577"  "1.089052207181701"  "-1.59423686272899"  "-.8756974097461611" "-1.456284630321612" "-2.029755912274617" "-2.118985941166938" "-1.488277265201495"
"01apr1983" ".6884390715830704"  ".2810884072434068"  "-.0996750844081976" "-.1942875429958722" ".0283160654422498"  ".4351364746015663"  "-.3906196261931087" "-.1813984998625786" "-.4532353878419163"
"01jul1983" "1.836577513532986"  "1.843757869197683"  "1.976310853898541"  ".8016757677076758"  "1.261657218151791"  ".8239182754299852"  ".8084951554321973"  ".9916124821698397"  ".5836947598905809"
"01oct1983" "1.50617711615224"   "1.482607160920874"  ".854458266526483"   "1.671181004729594"  "1.64141819429046"   "1.439544914024933"  "1.295385589935177"  "1.463690745084912"  ".5668417305682198"
"01jan1984" "1.610399396265523"  "1.185335587048806"  "1.214761959626268"  ".9453393170114359"  ".145515408935703"   "1.183053171611542"  "2.089556666781281"  "1.736592560134897"  "1.566823038041884"
end

So it is indeed a dataset with many series, and each series belongs to 1 specfic group. Thus, each group contains several series. In total there are 14 groups. Since the example cannot include all the series I show specific colums to illustrate this. If i understand correctly, I should therefore run the 1st code you have provided?

When I do so, I run into trouble when I do the following command:

foreach v of varlist `r(varlist)' {
char `v'[group] `=`v'[1]
rename `v' `=`v'[2]'
}

as I get the following error:

syntax error
Syntax is
rename oldname newname [, renumber[(#)] addnumber[(#)] sort ...]
rename (oldnames) (newnames) [, renumber[(#)] addnumber[(#)] sort ...]
rename oldnames , {upper|lower|proper}

Could you maybe have an idea why this is happening?

Best,

Ryan

[Clyde Schechter]

No idea. When I run the code from #2:

ds A, not
foreach v of varlist `r(varlist)' {
    char `v'[group] `=`v'[1]
    rename `v' `=`v'[2]'
}
drop in 1/2
gen int qdate = qofd(daily(A, "DMY")), before(A)
format qdate %tq
assert missing(A) == missing(qdate)
drop A
ds qdate, not
destring `r(varlist)', replace

with your example data in #4, I get no error messages. And it produces the results I believe you want:

* Example generated by -dataex-. To install: ssc install dataex
clear
input int qdate double(GDPC1 PCECC96 PCDGx IPFINAL IPCONGD IPMAT USPRIV MANEMP SRVPRD)
-1 -.8223981596001021  .3650269129167977   .216028379993192  .5164586799521738  .4669263794759714 -5.473431289722265 -.8710791702661019 -.5977236356222472 -.1896159530114201
 0  -.561912165990163 -1.038272296385466   -2.1248980673514 -1.262337723933731 -1.145523372720032  .7946468296534323 -.3731959171480947 -.0197528235492228  .4790178102485519
 1   1.80279703884361  .2330519053439989  .6462049897425028   2.20155760075351  2.515808462937025  4.082689673469607  1.471869992598859  2.239490886003505  1.161471148358175
 2 -1.569258492265201  .6894363614418856  .3311574486157206 -.4595035172364882  .0340225235151178 -2.695599651578648 -.5337584662697544  -.811144920092954  .4960302332017645
 3 -.3136409719859831 -1.815167574724039 -.7182466608899543 -1.410977789871182  -1.26564962017662 -1.319746207388967 -1.698630864444197 -1.068032672244793 -1.646363629073115
 4 -2.485586804542208 -1.012717500919827  -1.31699927459115 -1.280950703438864 -.8652131001342129 -2.334435741091222  -2.08802505558213 -1.518297429742752 -1.215309737658085
 5 -.0882738941519432 -1.265030523659988 -2.385659931921413 -1.520771166880837 -1.519714838393087 -1.288580235098284 -2.022680311966717 -1.569262012748853 -1.439174208884885
 6    1.1448204188911  1.028181956482924  .2635462626236992  1.423211318610236  2.402321515147232  2.422051319008141 -.3843683690929127  .6191043194578191 -.7447778992164527
 7  1.411110830318517 -.4741309717936375   .386784411896169  .9076962132731495  1.077616357683101  1.918831921713351  .6322240369817158  .9966294761815471  .7200854873621609
 8   1.46075396774132  1.806877163511684  1.184793236978319  2.012975168708311  1.900554655211966  1.528776199812922  .6686275824952422   1.07532188511387  .2581863662540683
 9  1.248116832146391  .3868891877851496  .2951560576894264  .6539905346539944   .119930940838851  .4974778289090573  .5197935381849891  .9945157005344126  .4052157371564657
10  .1885573693874193  .6415112640529802  .4690597982810318  1.127815467491937  .9201052615307053 -.5658130163804718  .7761063227161175  .7893244779891736  .8507110706215719
11  .5807787765656831   .006811979715034 -.1923909169116167  .5119226861829268  .2935775785368553  -.028375502546226  -.253937299388836  .1733699124028184 -.0721039789450881
12 -.5085992379083352   .918796140849536  1.038266785708293  .0467366183748433 -.0794272595787167  .1692703990984082 -.2933802504770557   .096525942492976 -.0632041002072682
13  .4156305360073915 -.1516759755249011  .2355007054356036  1.260191255665909  1.603407102070793   .540353830788342 -.4203015033871569  .1348840914081418 -.0318340693702387
14  .4524649620249028  .2134739499711086  .2871131404648894    .25141689638957  .6740265080435996  1.584312910266632  .4578794754149625  .4867847324201772  .3793304817094532
15  1.744454928822105  .8317360486024667 -.0199021398544998  .3410518210940962  .2347206439049873 -.2670172658664872  .0505040015808527  .2584728457823877  .1567431499786041
16  -.111831784155457  .0478190578910472  .0960184847336646  .6416292654468333  .7011061981779394  .5374707833282639  .1776324429477705  .3341022668551226  .8081187380318079
17  1.636765677148209  1.734484676308298  .8715861062185633  .3204895457841441  .4665075149358031  .4470847961387091  .3602238593229162  .3125095196043419  1.107008412884303
18  .4121287620134346  1.446477024834592  .4446847066132618    1.1677831140327  1.561432485213538  .8342302270501043  .2680750065780814  .4685541841885422  .2424660153561845
19  .9825064993879449  1.562889776954102   .676627587111945  .2952122694922396  .4128518978012652   .993393592550982  .9563659493127015  .9593206745456679  1.011996873468028
20  -.532888768308146 -.7770969052705949 -1.512988072012822  .3548265318680091 -.1105171695819052  .7486509431619455  .4773986829439928  .4183144228435438   1.02101654109992
21  2.008462897735706  2.131083902041099  2.617426787299745  2.537586006420369   3.00902057101774  1.432305565606911  1.105400855198736  1.542178144130728  1.004153102743358
22  .6226789644170452  .4522377815342461 -.3861478642061674  1.099504856581315  .3242676272586804  1.016923205596941  1.138257146192178  1.092479798301676  1.651377151364982
23  1.774114523088277   1.37106724902054  .7130208792045011  .9427959381060907  .2515722621925856  .7244805952796438  1.117584901014471  1.427405631596863  1.459605470002228
24  1.870503832652116  2.989559538698568  .9755940543694648   1.54384655932907  1.161016868087236  .1333044598362751  1.254297264490474  1.266651849354159  1.539718008904925
25  2.025698377922191  1.010392879788524  1.152529911690924  1.509621069930838  .7692960911592891  1.249298901321402  1.331831990822084  1.735579603709326  1.646949070022774
26 -.4930848699617391 -.8211857268176199  -1.84379319854661  1.093672635706918  .4363939357577618  .7682280695181556  1.464678684230938  1.857680002265755  1.995000218579468
27  .1198851573658766  .5106353127449059  .6656517350270938  .6461158296669397 -.3694278479492513  .5792360755719488  .9605838296921742  1.171643699431191  1.548349840762401
28  .0877744569305525 -.5791697797795964 -.4558543252893984   .778639567246231  .9688190546612273 -.1372471572061918   .383862607256282  .7511883962419427  1.385575503071429
29  .1666834744440063 -.3386850516552029 -1.099549109043046 -.0870013729796224 -.5366174070454388 -1.638371532448894  .1108807443093101  .1000307718019594  1.143022262789211
30 -.8345076795758143  .8474874681438693  1.004043927394374 -.1396006768750944  -.175873729307018 -1.138647310645758 -.6137519981608356 -.6407679397612239  .6132683517855088
31  .2398981887856186 -.4353933021056668 -.7196139345991366 -.5672579727111788 -.2853738046957144  .2514506619912872  .3357023942450564  .0082769381074943  1.076515367610516
32  .0074364970861345 -.2848750319926338 -.3564450751428609  1.564337838017924  2.453625842780276  1.084143617616065  .6031386634222528  .6188426008825076  .9622458585330522
33  1.554531034252141  2.377398164886389   1.62804917321992   .424852216013014  .3640888957267546  .5369847480594759  .3807073103228038  .6551926356567964  .7338543182205797
34  1.112814450505291  1.079740584848722  .2821693962910181 -.0042945752837207  .3588119659436307  .7046766373904779  .7418192433991091  .7121404792100634  .8595439549111918
35   .032810426474949  1.603033303706275  1.070251999025629  .2389785562885361  .5914474266691886 -.0761818974070856  .7184313478271648  .4368649633428986  1.315464196325725
36 -.4302425119839238 -.5272002841166726 -.6443923088454776  .5830278751838249  1.116332986543495  .3329859505508665  1.006127198149022  .5264550350374205   1.37104685233077
37  .9858786711714366  .4610824734080385  .2025424060886231  .5410492314684284  .6641789755556187  .7387358110470471   .986404997103757  .7905929200656672  1.155193391083467
38 -.5388325190152004 -.2350112605413687 -.5326506502741893 -.6811157709493809 -1.025772755517903   .272439177771396  .9540151250888288  .5398331223884067  1.274160269570253
39 -.1058715949049002   -.47225354082037 -.5377877348121621  .4135536781822071  1.046610897288119  .4498485897314137  .6082265650825787  .4687780390297291  .7422755485128893
40 -1.505840556321441 -.0032792055477978 -.5716599444605845 -1.374955566658383 -1.171701104995608 -.3394796958096653 -.2142378697917025 -.6705124840806901  .6581068913075736
41 -1.090587903849204 -.2720469868260188 -.9710421678446456 -1.820075201565137 -1.334510049814613 -1.891144656514812 -.5009220934776245  -.605337243838205  .2794176682279384
42 -.7361906092987534 -.5242973035531585 -.0456596474901753 -.5078448531284984  .4581330074816028 -.9783797440278649 -1.498807680301817 -1.529115154676207 -.2874376469907878
43   .210207826081956  .1130394350764603 -.3166481963767204  -1.44179288311444 -.8836275074571528 -.0021119012186352 -1.334853052945177 -1.287930865715752 -.9341646158878365
44 -2.224055880749505 -1.620372641016939 -2.806421990614395 -2.199152862090856 -1.193869893725983 -1.438427619074976 -2.143786966346394 -3.070243846525367 -.4190465839236563
45  2.360902325807622  1.670423139635876  3.057171010330006  .9142061037302394  2.736924401081062  .7894196334395512 -.1270939144104377  .3232777877391231 -.1985798012697597
46 -.2507074088381729  .1766471061234129   .328580366379263 -.2128361498588056  .6061988733516271  .4024530515739445 -.0097122325588947 -.1641326484065955  .0170160198472743
47  .0909363661072287 -.0062275272890239   .458963855892864  .5331250288909077  .8364499951151119 -1.027399360751526 -.1780974386255376 -.1316840131181257 -.1489282770590319
48 -.6218681610712521  1.283207030474974  .9944291271265092  1.103154038740327  1.386303881313165  .7032747645100372  .1839959230503055  .2456657561412073  .3510193865252542
49  1.314464818756735  .7767700902324838  .3354374728116997  1.686122009892167   1.56608555493787  2.284056266997474  1.137372097545448  1.104779649172485  1.347811770441307
50  1.829602368145429  1.646850410716824  .4101759947300904  .6896546235727649  .4524668747308068  .7062384675965233  1.167340164105435  1.199941875858933   1.15788162484112
51  .2377926892531946  1.107098376934679   .428906811726049  .6677466090008936  .8039744925014585   .053441767463545  .4735474005031615  .6145865535729049  .7159249939441698
52  1.117197720356583  2.303708941197178  1.329887898843835   2.10390402702979  1.824019958413872  1.410533629089275  1.481004525461084  1.718798966931193   1.40734657530358
53  2.074033621465553   1.53101542837176  1.678008824561743  1.343223238965412   .734571394079734  1.241651128125494  1.753129614730145  1.913122015119566  1.317564643158779
54  .4120646084776839 -1.277001348923534 -.8466701601345611 -.0036902538809489 -.5686973279468028  .1498952852251013  .8075814633905796  .9142855810646043  .5817451301946696
55  -1.55271543284473 -.6766793245606977 -.7415853243807145  .0113604225299749 -.8286919536809572  .1576937250977659  .3006579068424715  .2916763245256961  .0351248383316524
56  .2436369324857551 -1.661023845133604 -1.241584833852614  .6498867375962222  .1928856937794307  .4584540781221139  .8133370659624051  .8059575634137744  1.222652320289999
57 -1.962975894911905 -2.569592588775919 -1.239026180608115 -1.587110302065288 -2.314622738485986 -.5591491761406654 -.0228652783728707 -.0537385801507027  .3303120303651009
58 -.6195577226991309 -.6644845282829209 -.2507623629004352 -.1872199424074464 -.0633616261763773 -.3855919552796407 -.1970588610957462 -.1416214645722383  .2710872139759023
59 -2.068101386365702  -.559644869010042 -.2487190006498987 -.1436878602757629 -.2804984442081077  -.642834719374213 -.7518168172029508 -.5058989096204273  .2645194535883559
60 -1.383831232650698 -3.438485104267311 -3.895517472196093 -2.305105018798505 -3.044079174280717 -2.743587585805639 -2.281512511417233 -2.506830622285166 -.4675320682243309
61 -2.403861247556762  .0560660247270305  .2551710630258799 -5.336096976545016 -5.135741701158521 -3.619072667390395 -4.357034391505324 -4.910168741994094 -1.780744891254918
62  -.040386253538852  1.261786605897996  .2012116200195923 -.0356088661596103    1.5581516381615 -1.516269106294578 -2.057583548254923 -1.701874230660267 -.9901555578156115
63   1.16183320954882  .9282735592420885  1.632092038517535  1.687073610502896  2.999181832075681  .6919919041894574  .5204450357136272  .4487925454925327  .5994125628194096
64  .7229453307602188   .410925863931661  .5460136144076979  .7634567921950708  1.025461703045524  .9517282972966363  .9300436262259764  1.382652006913704  .2250318221269887
65  1.804543471406726  1.789012314248477  1.247653409270552  1.220007766209879  1.289980151618701  1.457761234816308  1.823257830542517  1.792049098732334  1.399410794705973
66 -.0171829741457432  .1781129182920684 -.2786472943221697  .1241776156835171  .1374139428240296  .4515425888326923  .6696865497158497  .8151045268802554  .4407461948745565
67 -.2414728908563904  .3772494396542608 -.0479463001314934   .267518041119184   .247615033973248  .3135374802475548   .228397937896225  .4301424715078562  .0745045226851586
68 -.0279037363710222  .7433811881985081  .0983993067739098  1.297830596290703  1.401194892561662  .2679481124771668  .3314459663119015  .3510341784087355  .2015596049138886
69  .5240879357434508   .565695041835729  .8283949453597093  1.345436097130412  1.042740210104177  .5790489043033272  1.359344593025917  1.145077609825004  .7730123091890186
70  1.440405741921039 -.3898342487475266  .3585867896362712  1.378825195008506  .7097992965094305   1.21378781644796  1.729590613896483  1.293943227120418  1.345216579495568
71  1.272994114667969  .2163788627599916  .0579986470869157  .6061271192771271  .2532965780699745 -.0212445383886281  1.323362666795473  .9682448314307274  2.015946835601662
72 -.9065856865106182  1.051087923229957  .2702198453418733  .0244460823015055  .6594439845703094 -.0309854360481268  1.240615304446573  .7832303609151381  1.903841504424795
73 -.5203774256724816 -.3603944008469778 -1.368915459630079 -.0568133435544016  -.546012689827368 -.8421619538354819  .9711124063067927  1.078878437717888  1.572280813840822
74  3.717521382266249  1.994452586564669  2.029065865419627  2.077818550133834  1.365629808332268  1.886077226423514  2.510012050226043  1.040808420238628  2.334276519324837
75  .3116727207332497 -.5742037512978059 -.9068000128614087  .2483351991067582 -.6865816213906712  .0797292839633688  1.241377022305377  .8392578567178188  1.118548492436664
76  .7201022605358369  .0082990718208445 -.1681939652244558  .7591324741510891 -.1970934590659337  .5743585176304683  1.300044192419472  1.177451919339933  1.036884668333553
77 -.6900588360833861 -.4264559898412105  -.844940410121049  .2365598891511857 -.6373850764070813 -.3553516215008249  .8954000135745985  .8711137264462616  .9351868797182298
78 -.7788372127951164  -1.30708522382794 -1.223489805063096  -.947850755575068 -1.516113094768355 -.2868157634378658  .6246039310626085  .4750042754481739  .2904258607686696
79 -.0060650800156641  .2576835507497248  .5058599426656887 -.3261285855335453  -1.38494622721359 -.6313536221943844  .0448466262190429 -.1301016830847165  .3986039247423862
80 -.6044669480603616 -.8324074994883689 -1.337397896369273 -.0438873655547308 -.2630216825461145 -.2316948558150187 -.0403025121237293 -.5246577489765402  .1942565902215382
81 -.5260490380507301  -1.42534357567006  -.987253345265836   .148471899554114 -.7117749015525289 -.1205172899991189 -.1876835181693317 -.3471396357571267  .1993596046196885
82 -3.449953052657735 -4.657974857582793 -4.364649786811445 -2.544692173294344 -3.109206562616639 -2.725227366111575 -2.215837885269128 -2.328545496601495 -.6178211145846844
83 -1.054373828042152  .4433066902882825  1.051562784679944 -.7150162189760938 -.7764854859650944 -1.658912778743271 -1.371982696965763 -1.620695374124819 -1.410901417894231
84  1.345582137081526  .8234674590138357  .5923410920163286  1.558261642107388  1.001982214794575   1.85877145199951  .7939956786568493  1.061117641945696  .1201110228892378
85  1.458753759175666 -.7022921580709627  .5230786083406452 -.2962387207613724 -.3393499514636585 -.2044252610107229   .255526541919099  .3171050125146783  -.134166231517033
86 -1.816971507102129 -1.197697084752666 -1.805391093560176  .2536025509266874  .0328969796690201 -.4274104488055866  .0859570016701066  .6589667165951514 -.6588849399931201
87  .5434223943035914 -.5098691944679384  .5861214071799972 -.0708343243547667 -.1425273861912506  .3650185325566671 -.0710958569252571  .0400953780451027 -.9074414431546456
88 -2.246141354568079 -2.248065097353864 -3.008664602207005 -.6876613485272993 -.6363734716370943 -1.974801037276585 -1.505647776798215 -1.607645501080034 -1.424513572938376
89 -2.819945183021569 -.0974175576656333  .6349895923030767 -1.543544387349028 -1.077352109695092 -1.564145008822724 -1.969534886883729 -2.080406398705917 -1.702292243465333
90 -.3538168074006757 -.7481796823212296 -.3221311708554751  -.664302298168191  .1710325421936461 -1.364984890732794 -1.723985694668586 -1.914277700495513 -1.435755034694163
91 -1.376669660792749 -.1969757132320966 -.1697314715436273 -1.095022314606156  .0345860980797172 -1.342265262136346 -2.008833995687508 -1.968130299806831 -1.878700179103738
92 -.8605220127482057  1.420813768752577  1.089052207181701  -1.59423686272899 -.8756974097461611 -1.456284630321612 -2.029755912274617 -2.118985941166938 -1.488277265201495
93  .6884390715830704  .2810884072434068 -.0996750844081976 -.1942875429958722  .0283160654422498  .4351364746015663 -.3906196261931087 -.1813984998625786 -.4532353878419163
94  1.836577513532986  1.843757869197683  1.976310853898541  .8016757677076758  1.261657218151791  .8239182754299852  .8084951554321973  .9916124821698397   .583694759890581
95   1.50617711615224  1.482607160920874   .854458266526483  1.671181004729594   1.64141819429046  1.439544914024933  1.295385589935177  1.463690745084912  .5668417305682198
96  1.610399396265523  1.185335587048806  1.214761959626268  .9453393170114359   .145515408935703  1.183053171611542  2.089556666781281  1.736592560134897  1.566823038041884
end
format %tq qdate

My best guess is that somewhere in the data set there is a series name that is not a legal variable name and that is causing the problem. But it isn't among the ones in your example. I suggest re-trying the code, but putting

set tracedepth 1
set trace on

in front of it. This will generate a lot of spurious output that is mostly of no interest. But, helpfully, when it hits the snag that is throwing the error message, you will be able to see exactly what the -rename- command looks like with the macros spelled out when it fails. When you do that, you may be able to figure out how to fix it yourself. Or, if not, post back showing the relevant section of the output and I'll try to figure it out.

[Mauricio Caceres]

Not sure how well this performs, but I think this is what you want?

mata A = st_sdata(., "A")'
mata X = st_sdata(., "*")'
clear
mata: stata(sprintf("set obs %g", rows(X) - 1))
mata: (void) st_addvar("str" :+ strofreal(colmax(strlen(X))), "v" :+ A)
mata: (void) st_sstore(., "v" :+ A, X[2::rows(X), .])
rename (vgroup vseries) (group series)
reshape long v, i(group series) j(datestr) string
destring v, replace
gen date = date(datestr, "DMY")
format %td_NNmonCCYY date

[Ryan Marapin]

Dear Statalist members,

It has been a busy week, until now I managed to get back to this post. I found my mistake and have tried out your code again Clyde, it worked perfectly, thanks allot! As for the other 2 posts from the other members, I will try this out now see how it tranforms my data. Thank you allot for your input as well.

Best,

Ryan