HELP-Variable id does not uniquely identify the observations & variable years contains all missing values

[NIck Stavr]

Hello I am trying to transform my wide data (5 countries,3 variables, quarterly data from 1966-2021)into long (panel) but when I try to run the command reshape long GDP CPI REC, i(ID) j(years) ,I get either: variable id does not uniquely identify the observations or variable years contains all missing values. I am really confused because I had approximately the same format in another dataset and reshape worked just fine.
Thank you in advance

[Clyde Schechter]

Well, Stata is telling you that your data are not compatible with the command as written. But you don't show us any example data--word descriptions of data are rarely adequate for troubleshooting. So I doubt anyone can say anything specific enough about this to be helpful. Please post back showing example data. Be sure to use the -dataex- command to do that so that all of the necessary information will be there. If you are running version 17, 16 or a fully updated version 15.1 or 14.2, -dataex- is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.

When asking for help with code, always show example data. When showing example data, always use -dataex-.

Added: When it comes to coding, nothing is ever "approximately the same" as anything else. There are no minor details in code.

[NIck Stavr]

This is part of my dataset, the values continue for 600blocks to the right after the GDPxxxxQx and change to CPIxxxxQx and then to RECxxxxQx where the x indicate years and the quarter. I tried using dataex but it says its not in my license. Thank you.

[Øyvind Snilsberg]

specify string option,

reshape long GDP CPI REC ,i(ID) j(years) string

[Nick Cox]

I tried using dataex but it says it's not in my license.

Clyde already addressed this in #2

If not, run ssc install dataex to get it.

(Minor editing of quotations)

Note: The implication is that you are using an old version of Stata. That shouldn't bite for use of reshape (whose syntax has been stable for some while) but for future questions the advice at https://www.statalist.org/forums/help#version may be crucial, together with the rest of the FAQ Advice.

[NIck Stavr]

I downloaded dataex but again it does not do something because of my license and I decreased observations from being quarterly from 1966 to yearly from 2000 and still dataex says try specifying fewer variables.
Also i ran the reshape long GDP CPI REC, i(ID) j(years) string but the result is the same. Does it play role that the ID variable is at the end of the data or is there anything else i can do about this?
Thank you in advance

[Øyvind Snilsberg]

drop if missing(ID)
reshape long GDP CPI REC ,i(ID) j(years) string

[Nick Cox]

I can now see that you are using Stata 16. So you have dataex and there is no need to download it. The message it is giving is nothing to do with your licence (license): it is explicitly about linesize, which is not the same thing. It's the length of line you're implying as output.

If #7 doesn't solve it then

duplicates list ID years

[NIck Stavr]

#7 worked indeed!!! Thank you for that. I am also unsure what variable I should generate now in order to use -tsset- so stata knows I am working with time series.
Also is it possible to add another variable which is oil prices through my 1966-2021 period in the model? Because price of oil is the same for all countries across time I am unsure how to input it in state (i have it in excel in the same format i had my previous data) .
Thank you in advance you have been so helpful.

[Nick Cox]

So years is a string variable with values like 2000 -- or with values like 1966Q1 -- according to different screenshots in #6. Which is it?

[NIck Stavr]

There you will find a fraction of the data as it is now and the commands i used to generate the datevar, however when i try to set as a time series it does not allow me to do that because of repeated time values in the sample. Indeed i have every date 5 times because of my 5 countries. How can i overcome this so I can start generating statistics and tables. Thank you very much.

[NIck Stavr]

I edited the format for the years in excel in order to make it easier for datevar and yq. Before it was 1966Q1 now it is 19960101. I thought i should also mention this because I got you somewhat confused as i kept changing the formatting

[Nick Cox]

If you move back and forth between at least 3 versions of your date you are going to confuse us!

But from your latest it seems that you have a quarterly date variable in suitable form. Given that, from looking at

help tsset

you should see that you must specify a panel identifier for panel data, here the numeric country identifier ID I would guess.

tsset ID yq

These screenshots are a pain. That in #11 would have worked fine with dataex.

[NIck Stavr]

Also is it possible at this point to add another variable at this point which is oil prices through my 1966-2021 period in the model? I did not include it initially because the price of oil is the same for all countries across time I am unsure how to input it in stata so the program does not to take oil prices as country-ID (i have oil prices in excel in the same format i had my previous data ie. quarterly). Can I add this last piece of data in or shall I start over and add the price of oil in my dataset with the countries, and then 1 ID will be price of oil. Would that be correct?
I am so grateful you have been extremely helpful

[Nick Cox]

#14 is a problem for merge.