Reshaping Data - creating new variables for each year

[Henry Bae]

Hello,

I am very much new to STATA and would like to ask for your help in reshaping the data (Forgive me if my explanation is not sufficient or poor for you to understand). I have India's tariff data by years (2020-2017) listed by 8 digits of Harmonized System 2017 + additional two digits of dutytypecode. I'm trying to reshape the data by creating new columns for each year if the values match the 8 digits of the HS code (Please note that each year has a shorter and a longer list of 8 digits codes, so each year's data will not perfectly match with other years) and list the data that does not match with other years at the end of each year's list.

The original data goes like this:

Year TL (<--8digit code) DutyTypeCode AVDutyRate
2020 XXXXXXXX 02 30
2020 XXXXXXXX 50 0
2020 YYYYYYYY 02 30
2020 YYYYYYYY 50 0
. . .
. . .
. . .
2019 XXXXXXXX 02 30
2019 XXXXXXXX 50 0
2019 YYYYYYYY 02 30
2019 YYYYYYYY 50 0
2019 ZZZZZZZZ 02 20
2019 ZZZZZZZZ 50 0
. . .
. . .
. . .
2018 XXXXXXXX 02 30
2018 XXXXXXXX 50 0

What I want the data to be:

TL DutyTypeCode AVDutyRate20 AVDutyRate19 AVDutyRate18 AVDutyRate17
XXXXXXXX 02 30 30 30 30
XXXXXXXX 50 0 0 0 0
YYYYYYYY 02 30 30 - 0
YYYYYYYY 50 0 0 - 0
ZZZZZZZZ 02 - 20 - -
ZZZZZZZZ 50 - 0 - -

I'm also adding a screenshot of my browser window. I have created new variables for each year already but show the value in that year.

Thank you for your help in advance!

[Clyde Schechter]

replace Year = Year-2000
reshape wide AVDutyRate, i(TL DutyTypeCode) j(Year)

In the future, when showing data examples, please use the -dataex- command to do so. 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.

That said, make sure you have a good reason to do this. There is very little data management of analysis in Stata that will work well with this wide data layout. There are a few specifics things that do, but unless you know you are doing them, you would be better off leaving the data in its original long layout. Most Stata commands are optimized for long data, and many will only work with long layout.