Transposing date part into the rest of the panel

[Falco Wolf]

Hello all,

I have a very big dataset, that I want to transpose it into a panel. The thing is, I have the dates in a horizontal format, that go from year 2009 to 2019, daily.
How can I transpose the dates to conform with the rest of the panel?

* Example generated by -dataex-. To install: ssc install dataex
clear
input str10 A str11 B str100 C str29 D str19 E str20 F str19 G str13(H I) str17 J
"COMPANY_ID" "IQ_ID"       "IQ_COMPANY_NAME"                                                             "IQ_PRIMARY_SIC_INDUSTRY"       "IQ_PRIMARY_SIC_CODE" "IQ_COUNTRY_NAME" "IQ_COUNTRY_ISO_CODE" "IQ_CLOSEPRICE" "IQ_CLOSEPRICE" "IQ_CLOSEPRICE"
""           ""            ""                                                                            ""                              ""                    ""                ""                    "  1/3/2009"    "  1/4/2009"    "  1/5/2009"   
"534464066"  "IQ534464066" "Warehouse REIT plc"                                                          "Real estate investment trusts" "6798"                "United Kingdom"  "GB"                  "0"             "0"             "0"            
"531369527"  "IQ531369527" "Triple Point Social Housing REIT plc"                                        "Real estate investment trusts" "6798"                "United Kingdom"  "GB"                  "0"             "0"             "0"            
"530396941"  "IQ530396941" "Convenience Retail REIT"                                                     "Real estate investment trusts" "6798"                "Australia"       "AU"                  "0"             "0"             "0"            
"434664367"  "IQ434664367" "Supermarket Income REIT plc"                                                 "Real estate investment trusts" "6798"                "United Kingdom"  "GB"                  "0"             "0"             "0"            
"433874536"  "IQ433874536" "Residential Secure Income plc"                                               "Real estate investment trusts" "6798"                "United Kingdom"  "GB"                  "0"             "0"             "0"            
"431961933"  "IQ431961933" "Granite Point Mortgage Trust Inc."                                           "Real estate investment trusts" "6798"                "United States"   "US"                  "0"             "0"             "0"            
"430159025"  "IQ430159025" "AEW UK Long Lease REIT Plc"                                                  "Real estate investment trusts" "6798"                "United Kingdom"  "GB"                  "0"             "0"             "0"            
"428898270"  "IQ428898270" "The PRS REIT plc"                                                            "Real estate investment trusts" "6798"                "United Kingdom"  "GB"                  "0"             "0"             "0"            
"427255106"  "IQ427255106" "Safety, Income & Growth Inc."                                                "Real estate investment trusts" "6798"                "United States"   "US"                  "0"             "0"             "0"            
"423274414"  "IQ423274414" "Menivim - The New Reit Ltd"                                                  "Real estate investment trusts" "6798"                "Israel"          "IL"                  "0"             "0"             "0"            
"422895629"  "IQ422895629" "Globalworth Poland Real Estate N.V."                                         "Real estate investment trusts" "6798"                "Netherlands"     "NL"                  "0"             "0"             "0"            
"421266273"  "IQ421266273" "BriQ Properties Real Estate Investment Company"                              "Real estate investment trusts" "6798"                "Greece"          "GR"                  "0"             "0"             "0"            
"418598112"  "IQ418598112" "LXI REIT plc"                                                                "Real estate investment trusts" "6798"                "United Kingdom"  "GB"                  "0"             "0"             "0"            
"417877278"  "IQ417877278" "JBG Smith Properties"                                                        "Real estate investment trusts" "6798"                "United States"   "US"                  "0"             "0"             "0"            
"417715026"  "IQ417715026" "Impact Healthcare REIT PLC"                                                  "Real estate investment trusts" "6798"                "United Kingdom"  "GB"                  "0"             "0"             "0"            
"415741538"  "IQ415741538" "Mistral Gayrimenkul Yatirim Ortakligi A.S."                                  "Real estate investment trusts" "6798"                "Turkey"          "TR"                  "0"             "0"             "0"            
"415395909"  "IQ415395909" "Invitation Homes Inc."                                                       "Real estate investment trusts" "6798"                "United States"   "US"                  "0"             "0"             "0"            
"415264646"  "IQ415264646" "MORI TRUST Hotel Reit, Inc."                                                 "Real estate investment trusts" "6798"                "Japan"           "JP"                  "0"             "0"             "0"            
"414727132"  "IQ414727132" "KIP Real Estate Investment Trust"                                            "Real estate investment trusts" "6798"                "Malaysia"        "MY"                  "0"             "0"             "0"            
"414654783"  "IQ414654783" "GLAND Office Leasehold Real Estate Investment Trust"                         "Real estate investment trusts" "6798"                "Thailand"        "TH"                  "0"             "0"             "0"            
"414618807"  "IQ414618807" "Dasin Retail Trust"                                                          "Real estate investment trusts" "6798"                "Singapore"       "SG"                  "0"             "0"             "0"            
"414147999"  "IQ414147999" "Olimpo Real Estate SOCIMI, S.A."                                             "Real estate investment trusts" "6798"                "Spain"           "ES"                  "0"             "0"             "0"            
"410154309"  "IQ410154309" "Optimum III Value-Added Residential Socimi, S.A."                            "Real estate investment trusts" "6798"                "Spain"           "ES"                  "0"             "0"             "0"            
"409956000"  "IQ409956000" "Transcend Residential Property Fund Limited"                                 "Real estate investment trusts" "6798"                "South Africa"    "ZA"                  "0"             "0"             "0"            
"409576413"  "IQ409576413" "MIRAI Corporation"                                                           "Real estate investment trusts" "6798"                "Japan"           "JP"                  "0"             "0"             "0"            
"408200196"  "IQ408200196" "Civitas Social Housing PLC"                                                  "Real estate investment trusts" "6798"                "United Kingdom"  "GB"                  "0"             "0"             "0"            
"408102947"  "IQ408102947" "Sachem Capital Corp."                                                        "Real estate investment trusts" "6798"                "United States"   "US"                  "0"             "0"             "0"            
"405732666"  "IQ405732666" "Spear Reit Limited"                                                          "Real estate investment trusts" "6798"                "South Africa"    "ZA"                  "0"             "0"             "0"            
"405051319"  "IQ405051319" "Innovative Industrial Properties, Inc."                                      "Real estate investment trusts" "6798"                "United States"   "US"                  "0"             "0"             "0"            
"402951032"  "IQ402951032" "Megureit Israel Ltd"                                                         "Real estate investment trusts" "6798"                "Israel"          "IL"                  "0"             "0"             "0"            
"383001496"  "IQ383001496" "European Commercial Real Estate Investment Trust"                            "Real estate investment trusts" "6798"                "Canada"          "CA"                  "0"             "0"             "0"            
"382934118"  "IQ382934118" "Starlight U.S. Multi-Family (No. 5) Core Fund"                               "Real estate investment trusts" "6798"                "Canada"          "CA"                  "0"             "0"             "0"            
"382225278"  "IQ382225278" "Thailand Prime Property Freehold and Leasehold Real Estate Investment Trust" "Real estate investment trusts" "6798"                "Thailand"        "TH"                  "0"             "0"             "0"            
"379246833"  "IQ379246833" "Sakura Sogo REIT Investment Corporation"                                     "Real estate investment trusts" "6798"                "Japan"           "JP"                  "0"             "0"             "0"            
"378506331"  "IQ378506331" "Ooedo Onsen Reit Investment Corporation"                                     "Real estate investment trusts" "6798"                "Japan"           "JP"                  "0"             "0"             "0"            
"377683585"  "IQ377683585" "EPP N.V."                                                                    "Real estate investment trusts" "6798"                "Netherlands"     "NL"                  "0"             "0"             "0"            
"375708909"  "IQ375708909" "Intercontinental International Real Estate Investment Company"               "Real estate investment trusts" "6798"                "Greece"          "GR"                  "0"             "0"             "0"            
"374964903"  "IQ374964903" "Optimum RE Spain, SOCIMI, S.A."                                              "Real estate investment trusts" "6798"                "Spain"           "ES"                  "0"             "0"             "0"            
"373780152"  "IQ373780152" "Viva Energy REIT"                                                            "Real estate investment trusts" "6798"                "Australia"       "AU"                  "0"             "0"             "0"            
"371209527"  "IQ371209527" "Mitsui Fudosan Logistics Park Inc."                                          "Real estate investment trusts" "6798"                "Japan"           "JP"                  "0"             "0"             "0"            
"370487424"  "IQ370487424" "EC World Real Estate Investment Trust"                                       "Real estate investment trusts" "6798"                "Singapore"       "SG"                  "0"             "0"             "0"            
"369246747"  "IQ369246747" "Marimo Regional Revitalization REIT, Inc."                                   "Real estate investment trusts" "6798"                "Japan"           "JP"                  "0"             "0"             "0"            
"369006419"  "IQ369006419" "Charter Hall Long WALE REIT"                                                 "Real estate investment trusts" "6798"                "Australia"       "AU"                  "0"             "0"             "0"            
"367054412"  "IQ367054412" "Hemaraj Leasehold Real Estate Investment Trust"                              "Real estate investment trusts" "6798"                "Thailand"        "TH"                  "0"             "0"             "0"            
"366733805"  "IQ366733805" "Quality Care Properties, Inc."                                               "Real estate investment trusts" "6798"                "United States"   "US"                  "0"             "0"             "0"            
"347586469"  "IQ347586469" "GMP Property SOCIMI, S.A."                                                   "Real estate investment trusts" "6798"                "Spain"           "ES"                  "0"             "0"             "0"            
"347494126"  "IQ347494126" "Investore Property Limited"                                                  "Real estate investment trusts" "6798"                "New Zealand"     "NZ"                  "0"             "0"             "0"            
"345491138"  "IQ345491138" "Frasers Logistics & Industrial Trust"                                        "Real estate investment trusts" "6798"                "Singapore"       "SG"                  "0"             "0"             "0"            
"332342344"  "IQ332342344" "Parkway, Inc."                                                               "Real estate investment trusts" "6798"                "United States"   "US"                  "0"             "0"             "0"            
"331398308"  "IQ331398308" "Nomura Real Estate Master Fund, Inc."                                        "Real estate investment trusts" "6798"                "Japan"           "JP"                  "0"             "0"             "0"            
"328905428"  "IQ328905428" "Urban Logistics REIT plc"                                                    "Real estate investment trusts" "6798"                "United Kingdom"  "GB"                  "0"             "0"             "0"            
"328510837"  "IQ328510837" "Golden Ventures Leasehold Real Estate Investment Trust"                      "Real estate investment trusts" "6798"                "Thailand"        "TH"                  "0"             "0"             "0"            
"328457068"  "IQ328457068" "Star Asia Investment Corporation"                                            "Real estate investment trusts" "6798"                "Japan"           "JP"                  "0"             "0"             "0"            
"323495438"  "IQ323495438" "Virtus ETF Trust II - Virtus Newfleet Dynamic Credit ETF"                    "Real estate investment trusts" "6798"                "United States"   "US"                  "0"             "0"             "0"            
"323471283"  "IQ323471283" "Newpark REIT Limited"                                                        "Real estate investment trusts" "6798"                "South Africa"    "ZA"                  "0"             "0"             "0"            
"321577362"  "IQ321577362" "LaSalle LOGIPORT REIT"                                                       "Real estate investment trusts" "6798"                "Japan"           "JP"                  "0"             "0"             "0"            
"320307539"  "IQ320307539" "Highlands REIT, Inc."                                                        "Real estate investment trusts" "6798"                "United States"   "US"                  "0"             "0"             "0"            
"318652365"  "IQ318652365" "BHG Retail REIT"                                                             "Real estate investment trusts" "6798"                "Singapore"       "SG"                  "0"             "0"             "0"            
"318161486"  "IQ318161486" "MFC Industrial Real Estate Investment Trust"                                 "Real estate investment trusts" "6798"                "Thailand"        "TH"                  "0"             "0"             "0"            
"318089449"  "IQ318089449" "LH Hotel Leasehold Real Estate Investment Trust"                             "Real estate investment trusts" "6798"                "Thailand"        "TH"                  "0"             "0"             "0"            
"317520357"  "IQ317520357" "Xior Student Housing NV"                                                     "Real estate investment trusts" "6798"                "Belgium"         "BE"                  "0"             "0"             "0"            
"316731786"  "IQ316731786" "Schroder European Real Estate Investment Trust Plc"                          "Real estate investment trusts" "6798"                "United Kingdom"  "GB"                  "0"             "0"             "0"            
"316505926"  "IQ316505926" "Elanor Retail Property Fund"                                                 "Real estate investment trusts" "6798"                "Australia"       "AU"                  "0"             "0"             "0"            
"315889640"  "IQ315889640" "Regional REIT Limited"                                                       "Real estate investment trusts" "6798"                "Guernsey"        "GG"                  "0"             "0"             "0"            
"315638543"  "IQ315638543" "MGM Growth Properties LLC"                                                   "Real estate investment trusts" "6798"                "United States"   "US"                  "0"             "0"             "0"            
"315282200"  "IQ315282200" "Stor-Age Property REIT Limited"                                              "Real estate investment trusts" "6798"                "South Africa"    "ZA"                  "0"             "0"             "0"            
"313214613"  "IQ313214613" "Ichigo Hotel REIT Investment Corporation"                                    "Real estate investment trusts" "6798"                "Japan"           "JP"                  "0"             "0"             "0"            
"311182372"  "IQ311182372" "Aventus Group"                                                               "Real estate investment trusts" "6798"                "Australia"       "AU"                  "0"             "0"             "0"            
"310866830"  "IQ310866830" "Al-Salam Real Estate Investment Trust"                                       "Real estate investment trusts" "6798"                "Malaysia"        "MY"                  "0"             "0"             "0"            
"309577827"  "IQ309577827" "WHA Business Complex Freehold and Leasehold Real Estate Investment Trust"    "Real estate investment trusts" "6798"                "Thailand"        "TH"                  "0"             "0"             "0"            
"309122562"  "IQ309122562" "Four Corners Property Trust, Inc."                                           "Real estate investment trusts" "6798"                "United States"   "US"                  "0"             "0"             "0"            
"306671588"  "IQ306671588" "Clipper Realty Inc."                                                         "Real estate investment trusts" "6798"                "United States"   "US"                  "0"             "0"             "0"            
"305921276"  "IQ305921276" "Northstar Realty Europe Corp."                                               "Real estate investment trusts" "6798"                "United States"   "US"                  "0"             "0"             "0"            
"305355974"  "IQ305355974" "Japan Senior Living Investment Corporation"                                  "Real estate investment trusts" "6798"                "Japan"           "JP"                  "0"             "0"             "0"            
"304008995"  "IQ304008995" "Trajano Iberia Socimi, S.A."                                                 "Real estate investment trusts" "6798"                "Spain"           "ES"                  "0"             "0"             "0"            
"303482482"  "IQ303482482" "Automotive Properties Real Estate Investment Trust"                          "Real estate investment trusts" "6798"                "Canada"          "CA"                  "0"             "0"             "0"            
"302733979"  "IQ302733979" "Manulife US Real Estate Investment Trust"                                    "Real estate investment trusts" "6798"                "Singapore"       "SG"                  "0"             "0"             "0"            
"302141512"  "IQ302141512" "Garda Diversified Property Fund"                                             "Real estate investment trusts" "6798"                "Australia"       "AU"                  "0"             "0"             "0"            
"302132167"  "IQ302132167" "Dolmen City REIT"                                                            "Real estate investment trusts" "6798"                "Pakistan"        "PK"                  "0"             "0"             "0"            
"301244643"  "IQ301244643" "Amata Summit Growth Freehold and Leasehold Real Estate Investment Trust"     "Real estate investment trusts" "6798"                "Thailand"        "TH"                  "0"             "0"             "0"            
"293371850"  "IQ293371850" "Drum Income Plus REIT Plc"                                                   "Real estate investment trusts" "6798"                "United Kingdom"  "GB"                  "0"             "0"             "0"            
"292554198"  "IQ292554198" "KCR Residential REIT plc"                                                    "Real estate investment trusts" "6798"                "United Kingdom"  "GB"                  "0"             "0"             "0"            
"292391607"  "IQ292391607" "AEW UK REIT plc"                                                             "Real estate investment trusts" "6798"                "United Kingdom"  "GB"                  "0"             "0"             "0"            
"291275428"  "IQ291275428" "Samty Residential Investment Corporation"                                    "Real estate investment trusts" "6798"                "Japan"           "JP"                  "0"             "0"             "0"            
"290461795"  "IQ290461795" "Care Capital Properties, Inc."                                               "Real estate investment trusts" "6798"                "United States"   "US"                  "0"             "0"             "0"            
"290103394"  "IQ290103394" "Seritage Growth Properties"                                                  "Real estate investment trusts" "6798"                "United States"   "US"                  "0"             "0"             "0"            
"288621788"  "IQ288621788" "Actinver Casa De Bolsa SA De CV-Trust No. 1938"                              "Real estate investment trusts" "6798"                "Mexico"          "MX"                  "0"             "0"             "0"            
"288219415"  "IQ288219415" "Community Healthcare Trust Incorporated"                                     "Real estate investment trusts" "6798"                "United States"   "US"                  "0"             "0"             "0"            
"285706914"  "IQ285706914" "Jernigan Capital, Inc."                                                      "Real estate investment trusts" "6798"                "United States"   "US"                  "0"             "0"             "0"            
"285416033"  "IQ285416033" "Healthcare & Medical Investment Corporation"                                 "Real estate investment trusts" "6798"                "Japan"           "JP"                  "0"             "0"             "0"            
"283612029"  "IQ283612029" "Resource Income Opportunity REIT, Inc."                                      "Real estate investment trusts" "6798"                "United States"   "US"                  "0"             "0"             "0"            
"283453025"  "IQ283453025" "TPG RE Finance Trust, Inc."                                                  "Real estate investment trusts" "6798"                "United States"   "US"                  "0"             "0"             "0"            
"283154866"  "IQ283154866" "KKR Real Estate Finance Trust Inc."                                          "Real estate investment trusts" "6798"                "United States"   "US"                  "0"             "0"             "0"            
"280529952"  "IQ280529952" "Easterly Government Properties, Inc."                                        "Real estate investment trusts" "6798"                "United States"   "US"                  "0"             "0"             "0"            
"280354084"  "IQ280354084" "InfraREIT, Inc. (REIT)"                                                      "Real estate investment trusts" "6798"                "United States"   "US"                  "0"             "0"             "0"            
"280333396"  "IQ280333396" "Kenedix Retail REIT Corporation"                                             "Real estate investment trusts" "6798"                "Japan"           "JP"                  "0"             "0"             "0"            
"279969330"  "IQ279969330" "Keppel DC REIT"                                                              "Real estate investment trusts" "6798"                "Singapore"       "SG"                  "0"             "0"             "0"            
"279704584"  "IQ279704584" "LH Shopping Centers Leasehold Real Estate Investment Trust"                  "Real estate investment trusts" "6798"                "Thailand"        "TH"                  "0"             "0"             "0"            
end

[Nick Cox]

Sorry, but I can't make sense of this.

1. Much of the point about a data example using dataex is that it should run on someone else's machine. This one promises 10 variables and delivers 3.

2. Nothing in your example makes it clear how dates and years are held.

[Falco Wolf]

Sorry, let me try rephrasing:

I have updated the data frame.

In the first row, I have the variable names which give specific information of the company (ID; IQ Code, Name, Industry...) up to IQ_CLOSEPRICE, which is closing price of the exchanged stock for that day.

The second row is empty until you reach the "IQ_CLOSEPRICE" variable. Then it gives you a specific date (in american format). It starts at 1/3/2009 in the dataex, and goes all the way to 2019 in my real data set. So I think I need to first, create a variable for each of the "DAILY CLOSE", and then transpose the date variables, into one date variable to be able to create the panel, to concord with the rest of dataset.

* Example generated by -dataex-. To install: ssc install dataex
clear
input str10 COMPANY_ID str11 IQ_ID str100 IQ_COMPANY_NAME str29 IQ_PRIMARY_SIC_INDUSTRY str19 IQ_PRIMARY_SIC_CODE str20 IQ_COUNTRY_NAME str19 IQ_COUNTRY_ISO_CODE str13(IQ_CLOSEPRICE I) str17 J
"COMPANY_ID" "IQ_ID"       "IQ_COMPANY_NAME"                                                             "IQ_PRIMARY_SIC_INDUSTRY"       "IQ_PRIMARY_SIC_CODE" "IQ_COUNTRY_NAME" "IQ_COUNTRY_ISO_CODE" "IQ_CLOSEPRICE" "IQ_CLOSEPRICE" "IQ_CLOSEPRICE"
""           ""            ""                                                                            ""                              ""                    ""                ""                    "  1/3/2009"    "  1/4/2009"    "  1/5/2009"   
"534464066"  "IQ534464066" "Warehouse REIT plc"                                                          "Real estate investment trusts" "6798"                "United Kingdom"  "GB"                  "0"             "0"             "0"            
"531369527"  "IQ531369527" "Triple Point Social Housing REIT plc"                                        "Real estate investment trusts" "6798"                "United Kingdom"  "GB"                  "0"             "0"             "0"            
"530396941"  "IQ530396941" "Convenience Retail REIT"                                                     "Real estate investment trusts" "6798"                "Australia"       "AU"                  "0"             "0"             "0"            
"434664367"  "IQ434664367" "Supermarket Income REIT plc"                                                 "Real estate investment trusts" "6798"                "United Kingdom"  "GB"                  "0"             "0"             "0"            
"433874536"  "IQ433874536" "Residential Secure Income plc"                                               "Real estate investment trusts" "6798"                "United Kingdom"  "GB"                  "0"             "0"             "0"            
"431961933"  "IQ431961933" "Granite Point Mortgage Trust Inc."                                           "Real estate investment trusts" "6798"                "United States"   "US"                  "0"             "0"             "0"            
"430159025"  "IQ430159025" "AEW UK Long Lease REIT Plc"                                                  "Real estate investment trusts" "6798"                "United Kingdom"  "GB"                  "0"             "0"             "0"            
"428898270"  "IQ428898270" "The PRS REIT plc"                                                            "Real estate investment trusts" "6798"                "United Kingdom"  "GB"                  "0"             "0"             "0"            
"427255106"  "IQ427255106" "Safety, Income & Growth Inc."                                                "Real estate investment trusts" "6798"                "United States"   "US"                  "0"             "0"             "0"            
"423274414"  "IQ423274414" "Menivim - The New Reit Ltd"                                                  "Real estate investment trusts" "6798"                "Israel"          "IL"                  "0"             "0"             "0"            
"422895629"  "IQ422895629" "Globalworth Poland Real Estate N.V."                                         "Real estate investment trusts" "6798"                "Netherlands"     "NL"                  "0"             "0"             "0"            
"421266273"  "IQ421266273" "BriQ Properties Real Estate Investment Company"                              "Real estate investment trusts" "6798"                "Greece"          "GR"                  "0"             "0"             "0"            
"418598112"  "IQ418598112" "LXI REIT plc"                                                                "Real estate investment trusts" "6798"                "United Kingdom"  "GB"                  "0"             "0"             "0"            
"417877278"  "IQ417877278" "JBG Smith Properties"                                                        "Real estate investment trusts" "6798"                "United States"   "US"                  "0"             "0"             "0"            
"417715026"  "IQ417715026" "Impact Healthcare REIT PLC"                                                  "Real estate investment trusts" "6798"                "United Kingdom"  "GB"                  "0"             "0"             "0"            
"415741538"  "IQ415741538" "Mistral Gayrimenkul Yatirim Ortakligi A.S."                                  "Real estate investment trusts" "6798"                "Turkey"          "TR"                  "0"             "0"             "0"            
"415395909"  "IQ415395909" "Invitation Homes Inc."                                                       "Real estate investment trusts" "6798"                "United States"   "US"                  "0"             "0"             "0"            
"415264646"  "IQ415264646" "MORI TRUST Hotel Reit, Inc."                                                 "Real estate investment trusts" "6798"                "Japan"           "JP"                  "0"             "0"             "0"            
"414727132"  "IQ414727132" "KIP Real Estate Investment Trust"                                            "Real estate investment trusts" "6798"                "Malaysia"        "MY"                  "0"             "0"             "0"            
"414654783"  "IQ414654783" "GLAND Office Leasehold Real Estate Investment Trust"                         "Real estate investment trusts" "6798"                "Thailand"        "TH"                  "0"             "0"             "0"            
"414618807"  "IQ414618807" "Dasin Retail Trust"                                                          "Real estate investment trusts" "6798"                "Singapore"       "SG"                  "0"             "0"             "0"            
"414147999"  "IQ414147999" "Olimpo Real Estate SOCIMI, S.A."                                             "Real estate investment trusts" "6798"                "Spain"           "ES"                  "0"             "0"             "0"            
"410154309"  "IQ410154309" "Optimum III Value-Added Residential Socimi, S.A."                            "Real estate investment trusts" "6798"                "Spain"           "ES"                  "0"             "0"             "0"            
"409956000"  "IQ409956000" "Transcend Residential Property Fund Limited"                                 "Real estate investment trusts" "6798"                "South Africa"    "ZA"                  "0"             "0"             "0"            
"409576413"  "IQ409576413" "MIRAI Corporation"                                                           "Real estate investment trusts" "6798"                "Japan"           "JP"                  "0"             "0"             "0"            
"408200196"  "IQ408200196" "Civitas Social Housing PLC"                                                  "Real estate investment trusts" "6798"                "United Kingdom"  "GB"                  "0"             "0"             "0"            
"408102947"  "IQ408102947" "Sachem Capital Corp."                                                        "Real estate investment trusts" "6798"                "United States"   "US"                  "0"             "0"             "0"            
"405732666"  "IQ405732666" "Spear Reit Limited"                                                          "Real estate investment trusts" "6798"                "South Africa"    "ZA"                  "0"             "0"             "0"            
"405051319"  "IQ405051319" "Innovative Industrial Properties, Inc."                                      "Real estate investment trusts" "6798"                "United States"   "US"                  "0"             "0"             "0"            
"402951032"  "IQ402951032" "Megureit Israel Ltd"                                                         "Real estate investment trusts" "6798"                "Israel"          "IL"                  "0"             "0"             "0"            
"383001496"  "IQ383001496" "European Commercial Real Estate Investment Trust"                            "Real estate investment trusts" "6798"                "Canada"          "CA"                  "0"             "0"             "0"            
"382934118"  "IQ382934118" "Starlight U.S. Multi-Family (No. 5) Core Fund"                               "Real estate investment trusts" "6798"                "Canada"          "CA"                  "0"             "0"             "0"            
"382225278"  "IQ382225278" "Thailand Prime Property Freehold and Leasehold Real Estate Investment Trust" "Real estate investment trusts" "6798"                "Thailand"        "TH"                  "0"             "0"             "0"            
"379246833"  "IQ379246833" "Sakura Sogo REIT Investment Corporation"                                     "Real estate investment trusts" "6798"                "Japan"           "JP"                  "0"             "0"             "0"            
"378506331"  "IQ378506331" "Ooedo Onsen Reit Investment Corporation"                                     "Real estate investment trusts" "6798"                "Japan"           "JP"                  "0"             "0"             "0"            
"377683585"  "IQ377683585" "EPP N.V."                                                                    "Real estate investment trusts" "6798"                "Netherlands"     "NL"                  "0"             "0"             "0"            
"375708909"  "IQ375708909" "Intercontinental International Real Estate Investment Company"               "Real estate investment trusts" "6798"                "Greece"          "GR"                  "0"             "0"             "0"            
"374964903"  "IQ374964903" "Optimum RE Spain, SOCIMI, S.A."                                              "Real estate investment trusts" "6798"                "Spain"           "ES"                  "0"             "0"             "0"            
"373780152"  "IQ373780152" "Viva Energy REIT"                                                            "Real estate investment trusts" "6798"                "Australia"       "AU"                  "0"             "0"             "0"            
"371209527"  "IQ371209527" "Mitsui Fudosan Logistics Park Inc."                                          "Real estate investment trusts" "6798"                "Japan"           "JP"                  "0"             "0"             "0"            
"370487424"  "IQ370487424" "EC World Real Estate Investment Trust"                                       "Real estate investment trusts" "6798"                "Singapore"       "SG"                  "0"             "0"             "0"            
"369246747"  "IQ369246747" "Marimo Regional Revitalization REIT, Inc."                                   "Real estate investment trusts" "6798"                "Japan"           "JP"                  "0"             "0"             "0"            
"369006419"  "IQ369006419" "Charter Hall Long WALE REIT"                                                 "Real estate investment trusts" "6798"                "Australia"       "AU"                  "0"             "0"             "0"            
"367054412"  "IQ367054412" "Hemaraj Leasehold Real Estate Investment Trust"                              "Real estate investment trusts" "6798"                "Thailand"        "TH"                  "0"             "0"             "0"            
"366733805"  "IQ366733805" "Quality Care Properties, Inc."                                               "Real estate investment trusts" "6798"                "United States"   "US"                  "0"             "0"             "0"            
"347586469"  "IQ347586469" "GMP Property SOCIMI, S.A."                                                   "Real estate investment trusts" "6798"                "Spain"           "ES"                  "0"             "0"             "0"            
"347494126"  "IQ347494126" "Investore Property Limited"                                                  "Real estate investment trusts" "6798"                "New Zealand"     "NZ"                  "0"             "0"             "0"            
"345491138"  "IQ345491138" "Frasers Logistics & Industrial Trust"                                        "Real estate investment trusts" "6798"                "Singapore"       "SG"                  "0"             "0"             "0"            
"332342344"  "IQ332342344" "Parkway, Inc."                                                               "Real estate investment trusts" "6798"                "United States"   "US"                  "0"             "0"             "0"            
"331398308"  "IQ331398308" "Nomura Real Estate Master Fund, Inc."                                        "Real estate investment trusts" "6798"                "Japan"           "JP"                  "0"             "0"             "0"            
"328905428"  "IQ328905428" "Urban Logistics REIT plc"                                                    "Real estate investment trusts" "6798"                "United Kingdom"  "GB"                  "0"             "0"             "0"            
"328510837"  "IQ328510837" "Golden Ventures Leasehold Real Estate Investment Trust"                      "Real estate investment trusts" "6798"                "Thailand"        "TH"                  "0"             "0"             "0"            
"328457068"  "IQ328457068" "Star Asia Investment Corporation"                                            "Real estate investment trusts" "6798"                "Japan"           "JP"                  "0"             "0"             "0"            
"323495438"  "IQ323495438" "Virtus ETF Trust II - Virtus Newfleet Dynamic Credit ETF"                    "Real estate investment trusts" "6798"                "United States"   "US"                  "0"             "0"             "0"            
"323471283"  "IQ323471283" "Newpark REIT Limited"                                                        "Real estate investment trusts" "6798"                "South Africa"    "ZA"                  "0"             "0"             "0"            
"321577362"  "IQ321577362" "LaSalle LOGIPORT REIT"                                                       "Real estate investment trusts" "6798"                "Japan"           "JP"                  "0"             "0"             "0"            
"320307539"  "IQ320307539" "Highlands REIT, Inc."                                                        "Real estate investment trusts" "6798"                "United States"   "US"                  "0"             "0"             "0"            
"318652365"  "IQ318652365" "BHG Retail REIT"                                                             "Real estate investment trusts" "6798"                "Singapore"       "SG"                  "0"             "0"             "0"            
"318161486"  "IQ318161486" "MFC Industrial Real Estate Investment Trust"                                 "Real estate investment trusts" "6798"                "Thailand"        "TH"                  "0"             "0"             "0"            
"318089449"  "IQ318089449" "LH Hotel Leasehold Real Estate Investment Trust"                             "Real estate investment trusts" "6798"                "Thailand"        "TH"                  "0"             "0"             "0"            
"317520357"  "IQ317520357" "Xior Student Housing NV"                                                     "Real estate investment trusts" "6798"                "Belgium"         "BE"                  "0"             "0"             "0"            
"316731786"  "IQ316731786" "Schroder European Real Estate Investment Trust Plc"                          "Real estate investment trusts" "6798"                "United Kingdom"  "GB"                  "0"             "0"             "0"            
"316505926"  "IQ316505926" "Elanor Retail Property Fund"                                                 "Real estate investment trusts" "6798"                "Australia"       "AU"                  "0"             "0"             "0"            
"315889640"  "IQ315889640" "Regional REIT Limited"                                                       "Real estate investment trusts" "6798"                "Guernsey"        "GG"                  "0"             "0"             "0"            
"315638543"  "IQ315638543" "MGM Growth Properties LLC"                                                   "Real estate investment trusts" "6798"                "United States"   "US"                  "0"             "0"             "0"            
"315282200"  "IQ315282200" "Stor-Age Property REIT Limited"                                              "Real estate investment trusts" "6798"                "South Africa"    "ZA"                  "0"             "0"             "0"            
"313214613"  "IQ313214613" "Ichigo Hotel REIT Investment Corporation"                                    "Real estate investment trusts" "6798"                "Japan"           "JP"                  "0"             "0"             "0"            
"311182372"  "IQ311182372" "Aventus Group"                                                               "Real estate investment trusts" "6798"                "Australia"       "AU"                  "0"             "0"             "0"            
"310866830"  "IQ310866830" "Al-Salam Real Estate Investment Trust"                                       "Real estate investment trusts" "6798"                "Malaysia"        "MY"                  "0"             "0"             "0"            
"309577827"  "IQ309577827" "WHA Business Complex Freehold and Leasehold Real Estate Investment Trust"    "Real estate investment trusts" "6798"                "Thailand"        "TH"                  "0"             "0"             "0"            
"309122562"  "IQ309122562" "Four Corners Property Trust, Inc."                                           "Real estate investment trusts" "6798"                "United States"   "US"                  "0"             "0"             "0"            
"306671588"  "IQ306671588" "Clipper Realty Inc."                                                         "Real estate investment trusts" "6798"                "United States"   "US"                  "0"             "0"             "0"            
"305921276"  "IQ305921276" "Northstar Realty Europe Corp."                                               "Real estate investment trusts" "6798"                "United States"   "US"                  "0"             "0"             "0"            
"305355974"  "IQ305355974" "Japan Senior Living Investment Corporation"                                  "Real estate investment trusts" "6798"                "Japan"           "JP"                  "0"             "0"             "0"            
"304008995"  "IQ304008995" "Trajano Iberia Socimi, S.A."                                                 "Real estate investment trusts" "6798"                "Spain"           "ES"                  "0"             "0"             "0"            
"303482482"  "IQ303482482" "Automotive Properties Real Estate Investment Trust"                          "Real estate investment trusts" "6798"                "Canada"          "CA"                  "0"             "0"             "0"            
"302733979"  "IQ302733979" "Manulife US Real Estate Investment Trust"                                    "Real estate investment trusts" "6798"                "Singapore"       "SG"                  "0"             "0"             "0"            
"302141512"  "IQ302141512" "Garda Diversified Property Fund"                                             "Real estate investment trusts" "6798"                "Australia"       "AU"                  "0"             "0"             "0"            
"302132167"  "IQ302132167" "Dolmen City REIT"                                                            "Real estate investment trusts" "6798"                "Pakistan"        "PK"                  "0"             "0"             "0"            
"301244643"  "IQ301244643" "Amata Summit Growth Freehold and Leasehold Real Estate Investment Trust"     "Real estate investment trusts" "6798"                "Thailand"        "TH"                  "0"             "0"             "0"            
"293371850"  "IQ293371850" "Drum Income Plus REIT Plc"                                                   "Real estate investment trusts" "6798"                "United Kingdom"  "GB"                  "0"             "0"             "0"            
"292554198"  "IQ292554198" "KCR Residential REIT plc"                                                    "Real estate investment trusts" "6798"                "United Kingdom"  "GB"                  "0"             "0"             "0"            
"292391607"  "IQ292391607" "AEW UK REIT plc"                                                             "Real estate investment trusts" "6798"                "United Kingdom"  "GB"                  "0"             "0"             "0"            
"291275428"  "IQ291275428" "Samty Residential Investment Corporation"                                    "Real estate investment trusts" "6798"                "Japan"           "JP"                  "0"             "0"             "0"            
"290461795"  "IQ290461795" "Care Capital Properties, Inc."                                               "Real estate investment trusts" "6798"                "United States"   "US"                  "0"             "0"             "0"            
"290103394"  "IQ290103394" "Seritage Growth Properties"                                                  "Real estate investment trusts" "6798"                "United States"   "US"                  "0"             "0"             "0"            
"288621788"  "IQ288621788" "Actinver Casa De Bolsa SA De CV-Trust No. 1938"                              "Real estate investment trusts" "6798"                "Mexico"          "MX"                  "0"             "0"             "0"            
"288219415"  "IQ288219415" "Community Healthcare Trust Incorporated"                                     "Real estate investment trusts" "6798"                "United States"   "US"                  "0"             "0"             "0"            
"285706914"  "IQ285706914" "Jernigan Capital, Inc."                                                      "Real estate investment trusts" "6798"                "United States"   "US"                  "0"             "0"             "0"            
"285416033"  "IQ285416033" "Healthcare & Medical Investment Corporation"                                 "Real estate investment trusts" "6798"                "Japan"           "JP"                  "0"             "0"             "0"            
"283612029"  "IQ283612029" "Resource Income Opportunity REIT, Inc."                                      "Real estate investment trusts" "6798"                "United States"   "US"                  "0"             "0"             "0"            
"283453025"  "IQ283453025" "TPG RE Finance Trust, Inc."                                                  "Real estate investment trusts" "6798"                "United States"   "US"                  "0"             "0"             "0"            
"283154866"  "IQ283154866" "KKR Real Estate Finance Trust Inc."                                          "Real estate investment trusts" "6798"                "United States"   "US"                  "0"             "0"             "0"            
"280529952"  "IQ280529952" "Easterly Government Properties, Inc."                                        "Real estate investment trusts" "6798"                "United States"   "US"                  "0"             "0"             "0"            
"280354084"  "IQ280354084" "InfraREIT, Inc. (REIT)"                                                      "Real estate investment trusts" "6798"                "United States"   "US"                  "0"             "0"             "0"            
"280333396"  "IQ280333396" "Kenedix Retail REIT Corporation"                                             "Real estate investment trusts" "6798"                "Japan"           "JP"                  "0"             "0"             "0"            
"279969330"  "IQ279969330" "Keppel DC REIT"                                                              "Real estate investment trusts" "6798"                "Singapore"       "SG"                  "0"             "0"             "0"            
"279704584"  "IQ279704584" "LH Shopping Centers Leasehold Real Estate Investment Trust"                  "Real estate investment trusts" "6798"                "Thailand"        "TH"                  "0"             "0"             "0"            
end

[Nick Cox]

My apologies! I did not notice the scroll bar.

This seems to work on your example data.

foreach v of var * { 
    label var `v' "`=`v'[1]'"
}

drop in 1 

foreach v of var IQ_CLOSEPRICE-J { 
    local date = daily(`v'[1], "MDY") 
    rename `v' IQ_CLOSEPRICE`date' 
}

drop in 1 

reshape long IQ_CLOSEPRICE , i(COMPANY_ID) j(DATE) 
destring IQ_CLOSEPRICE, replace

Warning: the variable list IQ_CLOSEPRICE-J applies only to your example. Your full dataset will end with something other than J.

[Falco Wolf]

Nick,

It worked perfectly!
Thank you