Reshaping to wide format

[Bahre Kiros]

Hello,

I have data on daily income (in USD) for the years 2010 to 2016, measured in terms of YY-MM-DD format for each household based on household_id as a unique identifier. I now want to reshape the 'income' variable to a wide format. I expect my new variables to be structured like this:
household_id latitude longitude inc100101 inc100102 ........inc161231, where inc** stands for income on Jan1_2010 Jan2_2010 and goes upto Dec31_ 2016. But I found the error message "values of variable date not unique within household_id". How could I fix this problem. Thanks for helping. The dataex example is shown here for same dates in the first year.


[CODE] dataex household_id latitude longitude income date

 reshape wide income, i(household_id) j(date) string

----------------------- copy starting from the next line -----------------------

* Example generated by -dataex-. For more info, type help dataex
clear
input str14 household_id float income str10 date
"01010101601002" 18.47 "2010-01-01"
"01010101601002" 18.31 "2010-01-02"
"01010101601002" 17.44 "2010-01-03"
"01010101601002" 16.67 "2010-01-04"
"01010101601002" 18.61 "2010-01-05"
"01010101601002" 19.44 "2010-01-06"
"01010101601002" 18.94 "2010-01-07"
"01010101601002" 18.13 "2010-01-08"
"01010101601002" 17.52 "2010-01-09"
"01010101601002" 16.77 "2010-01-10"
"01010101601002"  15.8 "2010-01-11"
"01010101601002" 15.96 "2010-01-12"
"01010101601002" 16.76 "2010-01-13"
"01010101601002" 18.19 "2010-01-14"
"01010101601002" 19.62 "2010-01-15"
"01010101601002" 20.11 "2010-01-16"
"01010101601002" 20.08 "2010-01-17"
"01010101601002" 21.19 "2010-01-18"
"01010101601002" 21.19 "2010-01-19"
"01010101601002" 21.23 "2010-01-20"
"01010101601002" 21.27 "2010-01-21"
"01010101601002" 20.48 "2010-01-22"
"01010101601002" 20.41 "2010-01-23"
"01010101601002"  19.8 "2010-01-24"
"01010101601002" 19.87 "2010-01-25"
"01010101601002" 19.86 "2010-01-26"
"01010101601002" 20.38 "2010-01-27"
"01010101601002" 20.56 "2010-01-28"
"01010101601002" 21.03 "2010-01-29"
"01010101601002" 21.39 "2010-01-30"
"01010101601002" 20.98 "2010-01-31"
"01010101601002" 21.37 "2010-02-01"
"01010101601002"  21.9 "2010-02-02"
"01010101601002" 22.12 "2010-02-03"
"01010101601002" 23.37 "2010-02-04"
"01010101601002" 22.04 "2010-02-05"
"01010101601002" 21.88 "2010-02-06"
"01010101601002" 21.23 "2010-02-07"
"01010101601002" 20.11 "2010-02-08"
"01010101601002" 18.94 "2010-02-09"
"01010101601002" 19.98 "2010-02-10"
"01010101601002" 20.69 "2010-02-11"
"01010101601002" 20.76 "2010-02-12"
"01010101601002" 20.98 "2010-02-13"
"01010101601002" 20.89 "2010-02-14"
"01010101601002" 20.83 "2010-02-15"
"01010101601002" 20.53 "2010-02-16"
"01010101601002" 19.47 "2010-02-17"
"01010101601002" 19.43 "2010-02-18"
"01010101601002" 20.86 "2010-02-19"
"01010101601002" 20.34 "2010-02-20"
"01010101601002"  20.9 "2010-02-21"
"01010101601002"  20.1 "2010-02-22"
"01010101601002" 21.13 "2010-02-23"
"01010101601002" 22.65 "2010-02-24"
"01010101601002"  22.9 "2010-02-25"
"01010101601002" 21.23 "2010-02-26"
"01010101601002" 20.52 "2010-02-27"
"01010101601002" 19.34 "2010-02-28"
"01010101601002" 20.69 "2010-03-01"
"01010101601002" 20.34 "2010-03-02"
"01010101601002" 19.52 "2010-03-03"
"01010101601002" 20.33 "2010-03-04"
"01010101601002" 20.81 "2010-03-05"
"01010101601002" 20.57 "2010-03-06"
"01010101601002" 21.42 "2010-03-07"
"01010101601002" 20.63 "2010-03-08"
"01010101601002" 20.21 "2010-03-09"
"01010101601002" 19.12 "2010-03-10"
"01010101601002" 19.44 "2010-03-11"
"01010101601002" 19.16 "2010-03-12"
"01010101601002" 19.91 "2010-03-13"
"01010101601002" 21.08 "2010-03-14"
"01010101601002"  22.1 "2010-03-15"
"01010101601002" 22.45 "2010-03-16"
"01010101601002" 22.77 "2010-03-17"
"01010101601002" 21.85 "2010-03-18"
"01010101601002" 21.68 "2010-03-19"
"01010101601002" 22.37 "2010-03-20"
"01010101601002" 21.51 "2010-03-21"
"01010101601002" 21.62 "2010-03-22"
"01010101601002" 21.48 "2010-03-23"
"01010101601002" 21.87 "2010-03-24"
"01010101601002" 22.71 "2010-03-25"
"01010101601002" 23.19 "2010-03-26"
"01010101601002" 22.27 "2010-03-27"
"01010101601002" 21.37 "2010-03-28"
"01010101601002" 21.06 "2010-03-29"
"01010101601002"  19.6 "2010-03-30"
"01010101601002" 21.32 "2010-03-31"
"01010101601002" 22.63 "2010-04-01"
"01010101601002" 21.99 "2010-04-02"
"01010101601002" 22.12 "2010-04-03"
"01010101601002"  22.4 "2010-04-04"
"01010101601002" 22.12 "2010-04-05"
"01010101601002" 21.92 "2010-04-06"
"01010101601002" 22.75 "2010-04-07"
"01010101601002" 23.08 "2010-04-08"
"01010101601002" 22.43 "2010-04-09"
"01010101601002" 23.52 "2010-04-10"
end

------------------ copy up to and including the previous line ------------------

[Nick Cox]

It’s hard to think of Stata tasks for which a wide layout would serve better than what you have. You are asking for thousands of new variables. Why do you think it would be a good idea?

[Bahre Kiros]

Hi Nick,

Thanks for the note. Actually, what I will finally have is panel data for the years 2010, 2013, and 2015. So, if you fix the case for the year 2011, that would be fine. Ignore the other years for the time being. Thanks!

[Bahre Kiros]

Hi Nick,

What I will finally have is a panel data for the years 2010, 2013, and 2015. So, if you fix the case for the year 2011, for example, that would be fine. Ignore the other years for the time being. Thanks!

[Bahre Kiros]

Sorry for the typos. I will finally have a panel data for the years 2010, 2013, and 2015. So, if you fix the case for the year 2010, for example, that would be fine. Ignore the other years for the time being. Thanks!

[Nick Cox]

If you think of your data as panel data, the existing layout is good. You will need a numeric date variable sooner or later.

[Bahre Kiros]

Thanks Nick. In each panel year, I have ten sections (9 in wide format and 1 in long).So, is not it odd if only one section has a different format to create and declare the final panel?