Double Reshape With Excel File?

[Jared Greathouse]

Hey everyone. I'm working with quite the interesting excel file. Let's load it, shall we?

clear *
cls
import excel "https://www.beerinstitute.org/wp-content/uploads/2021/12/2021-September-The-Brewers-Almanac-Beer-Institute-2021.xlsx", ///
sheet("Beer Shipments by State") cellrange(A3:MV55) clear
keep A CX-MV

qui foreach v of var CX-MV {
loc year: di `v'[1]
loc month: di `v'[2]

rename `v' shipments`year'_`month'
}
drop in 1/2
br

Okay, so we have total U.S. state imports of beer from 2000 to 2021. I want the monthly panel in long format, where each state is indexed to the year and month. But how? So far I've named the outcomes shipments_year_month... So I figured I might need to reshape twice? How might I make this a proper panel dataset?

[Justin Niakamal]

One approach

clear *
cls
import excel "https://www.beerinstitute.org/wp-content/uploads/2021/12/2021-September-The-Brewers-Almanac-Beer-Institute-2021.xlsx", ///
sheet("Beer Shipments by State") cellrange(A3:MV55) clear
keep A CX-MV

ren A state
ds state, not
foreach x in `r(varlist)' {
    ren `x' v`=`x'[2]'_`=`x'[1]'
}

drop in 1/2
reshape long v, i(state) j(date)string

gen month = mofd(date(date, "MY")), before(date)
format month %tm

destring v, replace
sort state month

[Jared Greathouse]

This does work, thank you so much!