(Stata13): How to Reshape Wide to Longitudinal Data

This hands-on tutorial shows how to reshape data from wide to longitudinal format in Stata13.
Here is the link to the engee2.xlsx dataset used for this tutorial (endeavour to have a Google account for easy accessibility): drive.google.com/drive/u/1/fo...
Follow up with soft-notes and updates from CrunchEconometrix:
Website: cruncheconometrix.com.ng
Blog: cruncheconometrix.blogspot.co...
Forum: cruncheconometrix.com.ng/blog/...
Facebook: / cruncheconometrix
KZbin Custom URL: / cruncheconometrix
Stata Videos Playlist: • (Stata13):Estimate and...
EViews Videos Playlist: • (EViews10):Interpret V...

[@CrunchEconometrix]

KZbin recently changed the way my content will be monetised. My channel now needs 1,000 subscribers. So it would be amazing if you show your support by both watching my videos and subscribing to my channel if you haven’t done so already. Monetising my videos allows me to invest back into the channel with some new equipment so this small gesture from you will be extremely huge for me. Many thanks for your support….CrunchEconometrix loves to teach, help me stay online.

[@CrunchEconometrix]

Hi Daniel, apologies for my very late response. You can employ the multiple imputation method which has its underlying assumptions. You may need to do a Google search on the procedure. Thanks.

[@natalievictoriaweber3335]

Thank you so much! I spent 17 hours last week trying to organize all my data manually, this just saved me

[@CrunchEconometrix]

Glad it helped Natalie!

[@GeniusOnHisWay]

I was stuck with getting Series into the wide format for so long. Thank you a ton!

[@CrunchEconometrix]

Glad to be of help, Simon!

[@Mzt1_]

this is life-saving. I was doing the manual mess and here I go enjoying the day because of this trick. thanks!

[@CrunchEconometrix]

U're welcome, Meney! Kindly tell others too!!!

[@Zaboomafoo996]

I'm so grateful for this video. I always refer back to it because it has simplified the process and made things so much easier for me. Thank you!

[@CrunchEconometrix]

Thanks Crawford, for the encouraging feedback. May God bless you!!!

[@mateosanchez1823]

You are amazing! You have helped me out so much in both of my thesis. Thank you so much for your insights!!

[@CrunchEconometrix]

Happy to help, Matteo! Please tell others too, thanks!

[@matildamoonga18]

This video has been a lifesaver 😭. LITERALLY. THANK YOU SO MUCH!

[@CrunchEconometrix]

Glad you find it helpful, Matilda...thanks for your encouraging feedback! 🥰🙏

[@okechukwuerinne2039]

This is the first time I am stumbling on a Nigerian doing stata analysis on KZbin. Thank you so much for this.

[@CrunchEconometrix]

You are welcome, Erinne. Please may I know from where (location) you are reaching me? Thanks.

[@StepsInc.]

Very brilliant and detailed explanation, here is to hoping you carry on!

[@CrunchEconometrix]

Thanks David, for the positive feedback and remarks on my video. Deeply appreciated! 😀

[@manooomanaaa2880]

I was struggling with this since 2 days, you just made it very simple and I did it in 10 minutes, thank you so much, lots of love and regards ❤️❤️❤️❤️

[@CrunchEconometrix]

Thanks, Manoo for the encouraging feedback. Deeply appreciated! 💗🙏

[@paulocheme6166]

Subscribed! Thanks alot for your detailed and simple explanations! It also feels awesome hearing these things explained in a Nigerian accent! Thanks

[@CrunchEconometrix]

Thanks Paul for your subscription. Deeply appreciated! I will really appreciate if you can help share the link to my KZbin Channel kzbin.info on your SM handles for awareness among my 9ja people and those in your network. Thanks again.

[@vb1406]

I love that you're from Africa! This is the by far the best videos I've seen on this, thank you so much for the great content!

[@CrunchEconometrix]

Yes ooo proudly African, Victor😊. I'm humbled and encouraged by your positive feedback on my videos. Deeply appreciated! 🙏 Please may I know from where (location) you are reaching me?

[@vb1406]

@@CrunchEconometrix I'm in grad school at an Ivy league school in the US, but born and raised on the motherland. Believe me none of my professors can explain this!

[@lakurelabpradeeppanthi2257]

This is a very useful video to reshape the dataset. Proud of you, professor.

[@CrunchEconometrix]

Thanks so much for your encouraging feedback, Doc! 🥰🙏

[@mahinurmimi9782]

I am highly grateful to you, Professor. Your video helps me a lot to conduct both my undergraduate research and MSS thesis. Huge respect for you ❣️

[@CrunchEconometrix]

Thanks, Mahinur for your encouraging feedback. Deeply appreciated 🥰🙏

[@morrismadutkon4590]

Thank you so much prof for your educative videos. You are doing a good job. very helpful indeed!

[@CrunchEconometrix]

Thanks for the good feedback, Morris...deeply appreciated! 💕 Kindly share my videos with your friends and academic community😊

[@Gustiishable]

I have been working on this exact problrm for 2 days straight, THANK YOU

[@CrunchEconometrix]

Happy to hear this, Gustii! 🥰

[@zoeynguyen6810]

Thank you for this video and the GMM series. Saved me time and helped my thesis 💐💐

[@CrunchEconometrix]

You are welcome, Zoey🥰

[@msizibuthelezi5579]

yoo is spent two days sorting my data thanks prof for this yooo am very grateful... from now on i will be using this for my panel data...

[@CrunchEconometrix]

U're welcome, Msizi!🥰🙏

[@bamworkitdougiepose]

YA'LL DOIN THE LORD'S WORK. TYSM FOR THIS U ARE SAVING MY THESIS!!!

[@CrunchEconometrix]

U're so welcome, Punch!

[@abrahamzakaria8805]

Thank you very much Prof. this is very important to me.

[@CrunchEconometrix]

Thanks so much for your positive feedback, deeply appreciated! ☺

[@hassaanahmad4770]

Thank you soo much Prof. i was exactly looking for this. stay blessed...

[@CrunchEconometrix]

Hahahaha Hassaan, my Channel got you and every researcher out there covered. Good to know that the clip is helpful. Please share this with your cohorts, students and academic community...gracias! :)

[@debasisdutta5890]

thank you so much professor for your kind and prompt suggestion and reply

[@CrunchEconometrix]

My humble pleasure, Debasis. Please may I know from where (location) you are reaching me?

[@heathprince5903]

Brilliant! Thank you!

[@CrunchEconometrix]

Hahahaha Heath, thanks for the positive feedback on my video. Deeply appreciated! 💕 Kindly share my Channel link with your students and academic networks! May I know where you are reaching me from?

[@speed21ization]

This is great! Thanks! Coming from a Jamaican!

[@CrunchEconometrix]

Awesome and thanks, Alvin! Kindly share my KZbin Channel link with your students, friends and academic community in Jamaica 🇯🇲! 💕 😊

[@kadirbulut8820]

Great help. Thanks a lot

[@CrunchEconometrix]

You are welcome, Sir.

[@kevinndenwadie8834]

Thank you so much for this tutorial. Helps me a lot. God bless you

[@CrunchEconometrix]

You are welcome, Kevin. Glad you find this very helpful 🙏🥰

[@jovanayap7443]

This video saved me!! Thank you so much for posting :)

[@CrunchEconometrix]

Thanks for the encouraging feedback, Jovana. Deeply appreciated! Please may I know from where (location) you are reaching me?

[@jovanayap7443]

CrunchEconometrix I’m from Canada!🇨🇦😊

[@wanjadouglas3058]

Very helpful. Thank you so much

[@CrunchEconometrix]

You're very welcome, Sir!

[@diywithlynk]

Thank you so much for making this video, I do learn alot from this. :-)

[@CrunchEconometrix]

Hi Hai, thanks for the encouraging feedback. Deeply appreciated! Please may I know from where (location) you are reaching me?

[@avestaabdulrahman6549]

Thank you, this is really helpful!

[@CrunchEconometrix]

Thanks for the encouraging feedback, Avesta!

[@capham8910]

Thanks you for your help, it's very fantastic. That's just intelligent work.

[@CrunchEconometrix]

Thanks, Pham for the encouraging feedback. Deeply appreciated!

[@Dr_Shiny]

Thank you so much. Love and respect from Beijing China

[@CrunchEconometrix]

U're welcome, Rain-Walker...kindly share my KZbin Channel link with your students, friends and academic community in China 🇨🇳. Thanks! 💕 😊

[@Dr_Shiny]

@@CrunchEconometrix Of course, i did. Thank you again.

[@CrunchEconometrix]

@@Dr_Shiny Deeply grateful, R-W! 💕 😊

[@muhammadsaqib3752]

really very helpful

[@CrunchEconometrix]

Thanks Muhd, for the encouraging feedback... deeply appreciated!

[@alessandrarizzi]

Thanks! Very helpful

[@CrunchEconometrix]

Thanks Lessa!!!..and please tell others about my YT Channel...

[@rowanadams4757]

You're literally amazing cheers crunch haha

[@CrunchEconometrix]

Thanks for the positive feedback, Rowan...appreciated!

[@Limbic_Life]

Thank you so much, this is incredibly helpful!!

[@CrunchEconometrix]

U're welcome, Hanna...may I know from where (location) you are reaching me?

[@Limbic_Life]

Currently I'm studying in the Netherlands :)

[@CrunchEconometrix]

My love to all Netherlanders...please tell your students, colleagues and academic community about my YT Channel!😍

[@armstrongasenavi719]

Thanks for this tutorial! It saved me lots of time.

[@CrunchEconometrix]

I'm encouraged by your feedback, Armstrong...love ya!!! May I know from where (location) you are reaching me?

[@armstrongasenavi719]

@@CrunchEconometrix Nairobi, Kenya

[@CrunchEconometrix]

@@armstrongasenavi719 Awesome Armstrong...I will appreciate if you can spread the word about my videos to your students and academic community in Kenya 🇰🇪! 💕 😊

[@miriam.msavane6807]

Thank you so much! Your video really help

[@CrunchEconometrix]

You're welcome, Miriam!

[@echobrasil]

Very helpful, thanx!

[@CrunchEconometrix]

You are welcome, Diogo!

[@92sags]

Thanks man.. you are a saviour..

[@CrunchEconometrix]

U're welcome, Bhaloo! 🥰🙏

[@junaidijunaidi3247]

I used quarterly data to test sticky cost (weiss, 2010). I have an osiris database that uses quarter data.

[@CrunchEconometrix]

Hi Junaid, that shouldn't be a problem. Follow the same steps to reshape the data and replace "year" with "quart" if "quart" is the time variable.

[@esayasmequanint9539]

Thank You!

[@CrunchEconometrix]

You are welcome, Esayas!🥰🙏

[@user-dp7lv8hu8x]

Thank you very very much!

[@CrunchEconometrix]

You're very welcome😊🙏

[@deeg1385]

Thank you Prof

[@CrunchEconometrix]

U're welcome, Darcy😊

[@abhideepkhare9249]

Thank you

[@CrunchEconometrix]

Glad you liked the video! 🙏🥰

[@markuslindvall6572]

Thanks! Very helpful :)

[@CrunchEconometrix]

Glad it helped, Markus!

[@shiyuanwang5743]

thank! . this is awesome!

[@CrunchEconometrix]

U're welcome 😊...glad to be of help! May I know from where (location) you are reaching me?

[@CrunchEconometrix]

U're welcome 😊...glad to be of help! May I know from where (location) you are reaching me?

[@linarakhadimullina6550]

thanks a lot

[@CrunchEconometrix]

You are welcome, Linara! 💕🙏

[@accountingdoctor2322]

Thank u thank u

[@CrunchEconometrix]

You are very welcome.

[@bayuhariyanto4539]

Thank you so much!!!!

[@CrunchEconometrix]

U're welcome, Bayu🙏. I'm glad you find this video helpful 😊

[@diamonddogsgaming7059]

God Bless you professor

[@CrunchEconometrix]

You too, Big Boss!!! Please may I know from where (location) you are reaching me?

[@diamonddogsgaming7059]

CrunchEconometrix I am an international masters student in economics currently studying in Norway. But I am from Pakistan. There’s a few things I need to ask you regarding stata. As I am currently in the process of writing my master thesis.

[@CrunchEconometrix]

No problem. Simply post your queries on the comment section of the respective KZbin video for others to benefit from the discussion. I'll do my best to guide. Thanks

[@diamonddogsgaming7059]

@@CrunchEconometrix Hi, I am also facing another issue regarding this video. I am doing an analysis on 20 countries across the world and I have a total of 12 variables and 10 years for each country. I followed all the steps you have listed in the videos but I eventually found a problem as I am missing 2 variables from my data set. Since I only have 10 years and 12 variables, when I go to rename my variables, 2 are missing. How can I fix this?

[@CrunchEconometrix]

@@diamonddogsgaming7059 I believe that you were able to get this sorted. Well done! 👏🏽

[@tizieyvesericbene4064]

good job

[@CrunchEconometrix]

...thanks Tizie, I'll appreciate if you tell your social media and academic community about by sharing the link to my KZbin Channel. My videos are really good...here's the link to myChannel cruncheconometrix.com.ng/youtube-videos/

[@thomashabanabakize4214]

Thank you very much. You are doing an outstanding job. would you please post a video on how to get data from IMF and World Bank

[@CrunchEconometrix]

Thanks Thomas for the positive feedback...deeply appreciated! I will log this request and do a video on it. Thanks!

[@asanteka.2403]

Good video and very helpful , please can you do a video on missen data estimation ? Notably by imputation. Thanks

[@CrunchEconometrix]

I'll do my best to do that video, Asante. Thanks for watching my videos...keep sharing too for more awareness😀

[@aymanissa6722]

The biggest LIKE

[@CrunchEconometrix]

Appreciated, Ayman... thanks! 🙏

[@nyakoelamech]

Thank you for this video! It is quite helpful. Could there be any problem if I decide to extract financial data from bank statements and simply prepare it in a long format/shape without necessarily having it wide and later reshaping to long? Now what happens with the series/variable names, do I have to reshape them to wide to make sense if at all whether I suggested makes sense?

[@CrunchEconometrix]

Hi Nyakoe, reshaping from W to L is the format allowed by Stata for panel data analysis. So, if your extraction yields a L-format data, then no need to reshape.

[@juliangang2956]

In case you really download WDI/World Bank data, you can actually define how you wanna have the data - you find that under "Layout"

[@CrunchEconometrix]

That's correct, Julian. Thanks

[@babaadam8639]

May thanks for the insightful videos. Please i would like to find out how to reshape string variables. Again, i would like to find out how to find averages in the long format.

[@CrunchEconometrix]

Have you tried out this procedure to reshape your string data? I don't quite understand the 2nd query?

[@dragobch]

Thank you very much for the video! I have a question: what should be the code in Stata if the date is not written in years , but in months and years, such as 1990/05 or may1990?

[@CrunchEconometrix]

Hi Lligramm, please what code are you referring to?

[@emanabdulhamid2385]

Thanks for this amazing video. it's my first time using stata and also my first time dealing with panel data . so, I have some question. why have the identifiers been generated for both country and series? does matter or not if used space in the commands? how to forecast a missing obs. using stata?

[@CrunchEconometrix]

Hi Eman, thanks for the encouraging feedback. Deeply appreciated! Stata will not execute w/o identifiers. I explained that.

[@pagi9866]

Thank you so much for that video it helped me a lot, I have a question: If i have dates in the excel format dd/mm/yyyy and I would like to use the day as they are in the same year what would I add as prefix. In the video because your dates where years you added yr do I just need to add d? or day?

[@CrunchEconometrix]

Hi Patick, thanks for the positive feedback. Deeply appreciated! Unfortunately I have not tried the code on a different date variant so may not be able to guide appropriately. My sincere apologies. Please may I know from where (location) you are reaching me?

[@pagi9866]

@@CrunchEconometrix Hey, okay thank you very much for your answer anyway, I am currently in China.

[@paulluhan]

I love you. Really.

[@CrunchEconometrix]

Hahahhaha Paul, love you too for enjoying my video. Deeply grateful. May I know from where (location) you are reaching me?

[@paulluhan]

CrunchEconometrix Burmese in Hong Kong. I had a hard time transferring data to Stata. Thx to you. I can move to other things.

[@CrunchEconometrix]

@@paulluhan Good to know Paul...I will greatly appreciate if you share my YT Channel link with your students and academic community. Thanks!

[@paulluhan]

CrunchEconometrix sure. I am still an undergraduate student.

[@user-nr5vw3vz1y]

Nice explanation ,would you mind making video making aranging pannel data only using excell nto through Stata?

[@CrunchEconometrix]

I have a video on BUILDING A PANEL DATA.

[@cyrilosabutey6204]

Thanks so much for the tutorial. Is there a continuation?

[@CrunchEconometrix]

Thanks for the positive feedback, Cyril. Deeply appreciated! This is a standalone clip. Kind regards.

[@akhliddinismailov3766]

Professor, I entered the following two commands:1. egen Banknum = group(Bank) 2. xtset Banknum Quarter, quarterly Is it enough to make the data panel data? Stata is saying that quarters are repeated and it's not doing stationary and other analysis by saying "repeated time values within panel" The quarters I entered normally belong to different banks so it should be normally accepted isn't it? Can you say what is wrong with my commands?

[@CrunchEconometrix]

Please follow my elementary guide as shown in the video. Prep your data from Excel before importing into Stata.

[@scorachikafa625]

You are a great teacher. And i have actually enjoyed most of your videos, especially the panel data subject. However, i have one question, How does one solve for the unbalanced panel data problem in stata for different variables involved?

[@CrunchEconometrix]

Hi Scora, thanks for the encouraging feedback. Deeply appreciated! Stata works perfectly with unbalanced data. Used several in my publications. Please may I know from where (location) you are reaching me?

[@scorachikafa625]

@@CrunchEconometrix i am reaching you from South Africa, Cape Town. Also i have followed your Rehape video from excel to stata importation. however, my data is giving me problem with the reshape command. It keeps on saying this "reshape long YR i(idc INDICATORNAME ) j(YEAR) option i() required " how do i rectify this please

[@CrunchEconometrix]

It appears you did not generate id for the cross-sections. In my video, I created country id called c_id.

[@dibyajyotisinha6379]

Amazing! Thanks .. I dont need to buy Tableau :D

[@CrunchEconometrix]

Hahahhahah, Dib...you owe me one then. Kindly share my link and videos with your colleagues and social media community.

[@Hidan94]

could u pls link the xlsx dataset again? Would be great! Great vid thx man.

[@CrunchEconometrix]

Hi Hidan, dataset is available upon payment of a token fee on my website cruncheconometrix.com.ng/shop/. But you can always practice along with yours. Thanks!

[@Sal9862]

How do i get the series data vertically in excel? I have it horizontal just like you have years, i can not assign a series id to them in this way

[@CrunchEconometrix]

I suppose that is how you downloaded it from the source. Then, you can use the TRANSPOSE command in Excel.

[@ulricharmelwawahyuniwo9783]

Thanks Doctor, I am blocked at the level of creating identifiers for my series... When I fit in the formula, it doesn't execute, rather it takes it as a text.

[@CrunchEconometrix]

Hi Wawah, perhaps the coding is wrong. Pls do it again.

[@anaisjager8360]

Thank you very much. Is there a way to download this data in the Stata form? Have you uploaded it?

[@CrunchEconometrix]

You are welcome, Anais... but the answer is no.

[@ECHTisaconcept]

Great video thank you! I am however stuck at the part where we reshape. Stata tells me: "option i incorrectly specified" could you help me with this?

[@CrunchEconometrix]

Hi George, thanks for watching my videos, deeply appreciated! Regarding your query, not exactly sure what the error is, but I'll say that you start all over and use my coding structure. Just do as I did.

[@ECHTisaconcept]

@@CrunchEconometrix are the country codes (the cc) important for stata?

[@ECHTisaconcept]

@@CrunchEconometrix for some reason mine only worked with curved brackets and not square brackets (for the reshape command)

[@CrunchEconometrix]

@@ECHTisaconcept Good to hear that it finally worked!

[@user-nr5vw3vz1y]

Please make video transposing data without stata

[@CrunchEconometrix]

Ok.

[@junaidijunaidi3247]

It is great video. How if I use quarter data Sir? I look in stata quarter symbol is quart, but I can't combain quater and year. Please help me Sir

[@CrunchEconometrix]

Hi Junaid, I'm wondering why you want to combine QUART and YEAR both defines the time variable in the data. Please watch the clip again and adapt to suit your data pattern.

[@dinobrown5956]

Can you please tell me how to select only six countries from the 46 countries in Stata? I usually use the filter tool in Excel to select the countries when I download data from the World Bank. But I want to do it in Stata but do not know the code to write. Kindly help. Thank you

[@CrunchEconometrix]

Dino, do it in excel.

[@beedeekenny9250]

Good day Prof, I hope this comment finds you well. I am an Honours student (Post-grad) working on a project and being the first time am collecting raw data by myself, seems a bit difficult. There's a particularly stubborn variable whose data displayed "abc" on the Eviews workfile and am sure it has meaning. Please kindly tell me what it means and what is likely to be the problem with the data? Thank you

[@CrunchEconometrix]

Beedee, I have no idea what your query is about.

[@DrImranrjn]

how i can use the Loop command to rename the multiple year names in one go. For example, we download a GDP of USA for Year 2000 to 2017, then how i can use the loop command to rename the year (2000, 2001, 2002) into (fy2000, fy2001, fy2002) in order to reshape the data. Please guide me.

[@CrunchEconometrix]

No idea.

[@DrImranrjn]

Thanks for your support, by the way i learn the STATA command to do it one go...@@CrunchEconometrix

[@CrunchEconometrix]

@@DrImranrjn Can you give me the command?

[@fatimaezzahrarachidi9999]

Thanks please how can I upload my half year data to Stata.

[@CrunchEconometrix]

Hi Fatima, same way you upload any data.

[@Gamen035]

I have too much dates on the first row for this as I have daily data instead of yearly, so State gives an error. Any suggestions?

[@CrunchEconometrix]

Hi Patrick, click on the error code and follow the guide on how to fix the problem.

[@asanteka.2403]

Hi prof Ngozi, i'm trying to import my data from excel to stata, but stata reads my annual time data as strings, please how Can i convert it to numeric data? Thanks in advance

[@CrunchEconometrix]

Hi Asante, I have a dofile (DF005) that addresses that but it is available on my website for a token fee. Kindly visit the STORE www.cruncheconometrix.com.ng/shop/ to make the purchase, thanks.

[@asanteka.2403]

@@CrunchEconometrix thanks very much

[@Dr_Shiny]

For all those who are facing issues with Quarterly Panel data............. 1- Create ID variables for each i 2- for quarters use 1,2,3,4,5...... instead of 2010q1, 2010q2, 2010q3..... Stata will give the error "so many missing values" 3- then run the above code 4- After reshape, just convert the 1,2,3,4,5..... Year variables into quarters, simple ... Thanks

[@CrunchEconometrix]

Thanks for this contribution. Grateful!

[@ayunisanurkhaqiqi4119]

Hi, can you guide me to do step 3 and 4? Im just begin to use stata, thanks before🙏

[@Dr_Shiny]

@@ayunisanurkhaqiqi4119 I could send via email

[@ayunisanurkhaqiqi4119]

My email [e-mail] thanks before

[@atusjim]

A nice video, the URL for your dataset is not opening, will you kindly help.

[@CrunchEconometrix]

Hi Atus, due to abuse and unethical conduct datasets used in my videos are no longer free (some are free though) but available on my website upon payment. Here's the link cruncheconometrix.com/view/datashop.php

[@Osegbue]

Thanks Dr. Please the code to generate c_id is very tiny. I cannot see it. Can you assist with the code?

[@CrunchEconometrix]

Hi Ifeanyi, aside typing the code, I also called out the cell names. Here is it: =if(B3=B2,A2,A2+1).

[@cengizkalyoncu4880]

Hi, first of all, I want to thank you for this useful video. I would like to ask a question about the problem I am facing. When I write to command row "reshape long yr, i (c_id s_id) j (year)" , there is no problem. But, when I write to command row "reshape wide yr, i(c_id year) j(s_id)" command, I am getting error that "variable s_id is string; specify string option". How can I solve this problem? Can you help me about this problem? I controlled my dataset and follow your this video's steps many times, but I couldn't solve this problem! If someone help me about this topic, I really will be glad.

[@CrunchEconometrix]

Hi Cengiz, I honestly have not experienced this before. Perhaps, the issue is with the s_id column. Click on the Stata error on tips on how to fix it. Thanks.

[@Dr_Shiny]

Prof, would you please guide me that how to enter "Quarterly" data i.e. Each year have 4 quarters, e.g. Q1 2018, Q2 2018, Q3 2018, Q4 2018. For banking level data.

[@CrunchEconometrix]

If the downloaded data is in quarterly, all you need to do is enter them as ditto for yearly data.

[@Dr_Shiny]

@@CrunchEconometrix Thank you Prof. Would you please share any youtube link for such Pannel quarterly data treatment?

[@CrunchEconometrix]

Unfortunately, none that I know of. Except you do an online search.

[@nasiryaseen1682]

whenever I try to export my excel data to stata it shows the "unexpected attribute" and can not load the excel sheet, please advise.

[@CrunchEconometrix]

Nasir, I honestly have no idea what the issue could be. You may want to check the underlying excel sheet for errors before importing to Stata.

[@samuelringmuhuboh5815]

Greetings crunch econometrics. I have an issue at the level of generating the s_id. At the level of inserting the formula it is refusing to apply the formula. What could be the problem. I need help

[@CrunchEconometrix]

Hi Samuel, if there are errors within your data, the code won't work. You should sort your variables first, then use the EXCEL formula.

[@jari976]

I'm strugling with getting my data right. Instead of one variable with longitudinal data I have 11, so I can't put yr in front of the specific year ,since I need to do it for 11 variables. e.g. ENVyr1, SOCyr1

[@CrunchEconometrix]

Jari, ensure your excel format is the same as mine.

[@jari976]

@@CrunchEconometrix yeah if i do that manually im formatting for 12hrs straight. Is there another way via stata, right now my data for 144 companies with a frame of 7 years is as follow: horizontal axes coloms: company, country, year, ESG, ROA, (control variables) vertical axes: year 2014-2020 (listed per company)

[@CrunchEconometrix]

Jari, it is advisable to download the data in the appropriate excel format before reshaping in Stata.

[@jari976]

@@CrunchEconometrix I used Refinitiv workspace, according the refinitiv support service of our university I can only extra it via excel in one format.

[@hamzaalmustafa9302]

Hi, and thanks for sharing this great tutorial video ! I have nearly same shape data, and i followed you strictly, but got this "variable id does not uniquely identify the observations" error. Any ideas? Thanks once again.

[@CrunchEconometrix]

Hi Hamza, thanks for the positive feedback. Deeply appreciated! Stata is always right 99% of the time. So, check your data again and create an id that is uniquely identifying.

[@hamzaalmustafa9302]

@@CrunchEconometrix Thanks for your replies. I solve it. I have another question, do you have any idea how to calculate the difference between maximum and minimum ROA over three overlapping years? Appreciated.

[@CrunchEconometrix]

Not at all.

[@percytaabazuing4554]

Hello Hamza, am also having the same issue, how did you resolve it?

[@stefannyguerra3464]

I get this error message: "variable id does not uniquely identify the observations Your data are currently wide. You are performing a reshape long. You specified i(s_id c_id) and j(year). In the current wide form, variable s_id c_id should uniquely identify the observations." do you know what to do?

[@CrunchEconometrix]

Hi Stefanny, not an issue. Follow the Stata for guides on how to correct the problem.

[@anaisjager8360]

I get the exact same error message and I have not been able to solve it. Could you please help me?

[@edgarndani5238]

i have tried this formula in stata but is bringing an error back saying my years.... not found

[@CrunchEconometrix]

That may be because your time variable is not in "years". Always know that Stata dofiles are to be modified to suit different studies....

[@amirshahzadkhanwattoo3939]

sir what problem we face if number of countries is greater than number of years?

[@CrunchEconometrix]

You will need to read about the technique you want to deploy to find out the econometric implications if you use the wrong panel data structure.

[@yasminlurusati4524]

hi I have followed your manual on the reshape. it was very useful. however, it still didn't work because whenever I tried in the stata to reshape the year, the command error appeared. could you help with this please? thanks

[@CrunchEconometrix]

Hi Yasmin, state the error you're getting.

[@yasminlurusati4524]

Hi, thank you for the reply!:) I was thinking of giving you the screenshot however I can’t upload it in the youtube. Is it possible to send it by email? I am giving the values to all the year like you did when creating the id and paste it in the stata with the hope the syntax will work however it still didn’t work:(

[@CrunchEconometrix]

@@yasminlurusati4524 OK send the screenshot to [e-mail]. I'll guide you on what to do.

[@CrunchEconometrix]

No girl, looking at your DE, you didn't follow my guide. Go to your excel sheet and delete the series code column and remove the double dots. Please play the video again and work along on your data.

[@Azam_Pakistan]

Cant we have the new variable names to be variants of the actual e.g 1980_ instead of yr1 ?

[@CrunchEconometrix]

No, you can't. As you noticed "yr1" is Stata-generated representing variable names and not years.

[@Azam_Pakistan]

@@CrunchEconometrix Thankyou

[@handecakr8775]

Hi, this is really a very useful video and gives what I need. I have similar data with 18 years and 44 institutions and and did exactly what you said, but it gives error as follows. "reshape long yr, i(b_id s_id) j(year) (note: j = 2002 2003 2004 2005 2006 2007 2008 2009 2010 2011 2012 2013 2014 2015 2016 2017 2018) variable id does not uniquely identify the observations Your data are currently wide. You are performing a reshape long. You specified i(b_id s_id) and j(year). In the current wide form, variable b_id s_id should uniquely identify the observations" can you help me? And how can we use it for quarterly data?

[@CrunchEconometrix]

Follow the same procedure. But modify to indicate the quarterly data.

[@handecakr8775]

@@CrunchEconometrix thanks, using date instead of year and additional comments solved my problem.Errors always arise from the problems of your data.

[@subaldanta7129]

Thank You very much. It would be a great help to me if you do the same by taking data from prowess.

[@CrunchEconometrix]

Subal, thanks for the positive feedback. What do you imply by "taking data from prowess"?

[@subaldanta7129]

@@CrunchEconometrix A database on Indian listed and unlisted companies.

[@CrunchEconometrix]

You can apply the knowledge to any data source.

[@kshitijpatil4403]

I am getting option i incorrectly specified r(198), when I do the reshape command in stata 15, can you please help?

[@diamonddogsgaming7059]

I think I know what you mean by this error. I was working on my data and I followed this video and it worked perfectly. But I made changes to my data (example number of years were reduced, number of countries added etc.) Now when I type in the same commands, it gives me the exact error as you have stated. I think its because of the fact that the number of variables are not equal to the number of years in our data. I have 12 variables and 10 years. Before I had 12 variables and 12 years so the commands worked perfectly. I have requested the user to give me a solution to this problem. Hopefully you're facing the same issue and it will be solved.

[@CrunchEconometrix]

Hi Kshitij, i is the cross-sectional identifier. You may need to watch the clip again and align your data correctly in excel before taking it to Stata. Please may I know from where (location) you are reaching me?

[@ideahubchiranjibigautam7845]

Thank you.

[@CrunchEconometrix]

U're welcome, Chiranjibi! 🥰

[@claretergu9801]

Hello! What if it’s one country, is an id needed? If yes, how to go about it please?

[@CrunchEconometrix]

No.

[@claretergu9801]

@@CrunchEconometrix thank you

[@eleftheriachadouli7139]

Great video and very helpful since I use stata for the first time. I have a similar dataset with countries and series and 3 years. I followe your video step by step but stata says there is an error "variable id does not uniquely identify the observations Your data are currently wide. You are performing a reshape long. You specified i(cid mid) and j(year). In the current wide form, variable cid mid should uniquely identify the observations. Remember this picture: ...." Can you please help me through it cause I dont understand what the problem is?

[@CrunchEconometrix]

What is your "mid"?

[@eleftheriachadouli7139]

@@CrunchEconometrix mid is your s_id! Specifically, it is some hr metrics like working hours, layoffs, etc

[@CrunchEconometrix]

@@eleftheriachadouli7139 Does it uniquely identify the cs? Because my series id (s_id) does just that.