Hello everyone.

I have a number of datasets (excel files) with data formatted as

SheetName: Year1,VarX
Name Cat1 Cat2 Cat3 Cat4
Name1 x1 x2 x3 x4
Name2 x5 x6x x7 x8
SheetName: Year2,VarX
Name Cat1 Cat2 Cat3 Cat4
Name1 x9 x10 x11 x12
Name2 x13 x14 x15 x16
SheetName: Year1,VarY
Name Cat1 Cat2 Cat3 Cat4
Name1 y1 y2 y3 y4
Name2 y5 y6 y7 y8
I'm trying to reformat the data so it looks like:
Year Name Cat X Y
1 Name1 Cat1 x1 y1
1 Name1 Cat2 x2 y2
1 Name1 Cat3 x3 y3
[...]
2 Name2 Cat2 x14 y14
2 Name2 Cat3 x15 y15
2 Name2 Cat4 x16 y16
I've looked at reshape and stack respectively but I don't think either of them do quite what is needed. While I could do this manually in Excel, I don't have the time to go through this for the thousands of data observations I'm working with. This feels like there should be a Stata command to do so, but I don't know what it would be.

Thanks