Conversion quaterly data to yearly

[Muhammad Salahuddin]

Hi,

Asking very silly question. Just I want convert the quarterly data to yearly. I was trying in different way, somehow, making mistakes. Thanks in advance

* Example generated by -dataex-. For more info, type help dataex
clear
input float id str3 country str6 year str3 eco_uncertainty
1 "ARE" "1996q1" "0" 
1 "ARE" "1996q2" "0" 
1 "ARE" "1996q3" "0" 
1 "ARE" "1996q4" "0" 
1 "ARE" "1997q1" "0" 
1 "ARE" "1997q2" "2" 
1 "ARE" "1997q3" "3" 
1 "ARE" "1997q4" "2" 
1 "ARE" "1998q1" "7" 
1 "ARE" "1998q2" "3" 
1 "ARE" "1998q3" "1" 
1 "ARE" "1998q4" "10"
1 "ARE" "1999q1" "1" 
1 "ARE" "1999q2" "3" 
1 "ARE" "1999q3" "0" 
1 "ARE" "1999q4" "0" 
1 "ARE" "2000q1" "0" 
1 "ARE" "2000q2" "5" 
1 "ARE" "2000q3" "2" 
1 "ARE" "2000q4" "0" 
1 "ARE" "2001q1" "0" 
1 "ARE" "2001q2" "1" 
1 "ARE" "2001q3" "0" 
1 "ARE" "2001q4" "0" 
1 "ARE" "2002q1" "0" 
1 "ARE" "2002q2" "0" 
1 "ARE" "2002q3" "0" 
1 "ARE" "2002q4" "4" 
1 "ARE" "2003q1" "1" 
1 "ARE" "2003q2" "2" 
1 "ARE" "2003q3" "0" 
1 "ARE" "2003q4" "0" 
1 "ARE" "2004q1" "0" 
1 "ARE" "2004q2" "2" 
1 "ARE" "2004q3" "1" 
1 "ARE" "2004q4" "1" 
1 "ARE" "2005q1" "0" 
1 "ARE" "2005q2" "0" 
1 "ARE" "2005q3" "0" 
1 "ARE" "2005q4" "1" 
1 "ARE" "2006q1" "4" 
1 "ARE" "2006q2" "3" 
1 "ARE" "2006q3" "1" 
1 "ARE" "2006q4" "1" 
1 "ARE" "2007q1" "2" 
1 "ARE" "2007q2" "1" 
1 "ARE" "2007q3" "0" 
1 "ARE" "2007q4" "0" 
1 "ARE" "2008q1" "1" 
1 "ARE" "2008q2" "1" 
1 "ARE" "2008q3" "1" 
1 "ARE" "2008q4" "1" 
1 "ARE" "2009q1" "0" 
1 "ARE" "2009q2" "0" 
1 "ARE" "2009q3" "0" 
1 "ARE" "2009q4" "0" 
1 "ARE" "2010q1" "0" 
1 "ARE" "2010q2" "1" 
1 "ARE" "2010q3" "0" 
1 "ARE" "2010q4" "3" 
1 "ARE" "2011q1" "3" 
1 "ARE" "2011q2" "2" 
1 "ARE" "2011q3" "2" 
1 "ARE" "2011q4" "2" 
1 "ARE" "2012q1" "1" 
1 "ARE" "2012q2" "1" 
1 "ARE" "2012q3" "6" 
1 "ARE" "2012q4" "4" 
1 "ARE" "2013q1" "3" 
1 "ARE" "2013q2" "1" 
1 "ARE" "2013q3" "1" 
1 "ARE" "2013q4" "4" 
1 "ARE" "2014q1" "1" 
1 "ARE" "2014q2" "4" 
1 "ARE" "2014q3" "2" 
1 "ARE" "2014q4" "1" 
1 "ARE" "2015q1" "2" 
1 "ARE" "2015q2" "0" 
1 "ARE" "2015q3" "0" 
1 "ARE" "2015q4" "0" 
1 "ARE" "2016q1" "1" 
1 "ARE" "2016q2" "0" 
1 "ARE" "2016q3" "2" 
1 "ARE" "2016q4" "5" 
1 "ARE" "2017q1" "7" 
1 "ARE" "2017q2" "4" 
1 "ARE" "2017q3" "1" 
1 "ARE" "2017q4" "6" 
1 "ARE" "2018q1" "4" 
1 "ARE" "2018q2" "2" 
1 "ARE" "2018q3" "5" 
1 "ARE" "2018q4" "3" 
1 "ARE" "2019q1" "3" 
1 "ARE" "2019q2" "1" 
1 "ARE" "2019q3" "1" 
1 "ARE" "2019q4" "1" 
1 "ARE" "2020q1" "1" 
1 "ARE" "2020q2" "4" 
1 "ARE" "2020q3" "5" 
1 "ARE" "2020q4" "1" 
end

[Nick Cox]

What does “convert” mean here? Total, mean, maximum, minimum, first, last of four values in each year? Or something else? Whichever it is, I guess you need to

destring sco_uncertainty, replace

and then collapse in some way.

[Muhammad Salahuddin]

What does “convert” mean here? Total, mean, maximum, minimum, first, last of four values in each year? Or something else? Whichever it is, I guess you need to

destring sco_uncertainty, replace

and then collapse in some way.

Thank again for your comments. I have used the codes to convert the data:

rename Country country
egen id = group (country)
sort id
order id country, be (year)
destring eco_uncertainty, replace
encode eco_uncertainty, gen (eco_uncertainty1)
destring year, replace
gen yearly = yofd(dofq(year))
collapse eco_uncertainty, by (id year country )

It shows "type mismatch". When encoding the year, it does not show the continuous year, only a static year. In that case, what should I do?
Also I want to keep only the data from 1996 to 2018. So which code should I use?

[Nick Cox]

type mismatch suggests that you are trying to taking a mean (which is what collapse does by default) of a string variable.

I infer that the destring failed, which is why you followed up with encode. But then you tried to collapse eco_uncertainty, which I guess is a still a string variable.

The problem isn't going to be solved by collapse eco_uncertainty1 because eco_uncertainty1 almost certainly contains garbage. For example, I can see in your data example that the values of eco_uncertainty there sort "0" "1" "10" "2" and so on, which will be mapped by encode to 1 2 3 4 and so on, which is, as said, garbage.

There seem to be two points in the air.

The first is that you didn't really reply to my question What do you mean by convert? except impiicitly that you're trying to collapse to a mean across four quarterly values. Is that right? Does it make sense? What is eco_uncertainty? Is it an integer score for which taking means is defensible?

The second is why destring failed, if it did, which can be illuminated by showing us the results of

tab eco_uncertainty if missing(real(eco_uncertainty))