Transpose (rotating) data by group (variable)

[John Zoey]

Dear all,

I need to transpose my dataset so that I can calculate the pairwise correlation matrix for all of my variables.

My data right now is similar to this:

Stock ticker	Time (day no #)	Stock return (%)
A	3	A1
A	4	A2
A	5	A3
A	6	A4
A	7	A5
A	8	A6
A	9	A7
B	1	B1
B	2	B2
B	3	B3
B	4	B4
B	5	B5
B	6	B6
C	5	C1
C	6	C2
C	7	C3
C	8	C4
C	9	C5
C	10	C6

And I want to transpose it to something similar to this, with A, B, and C as variables so that I can calculate the pairwise correlation matrix for them.

Date #	A	B	C
1		B1
2		B2
3	A1	B3
4	A2	B4
5	A3	B5	C1
6	A4	B6	C2
7	A5		C3
8	A6		C4
9	A7		C5
10			C6

As for the blanks, I want Stata to treat them as missing value "." as well. For my real dataset, I will have 412 stock tickers.

Any help is appreciated! I'm using Stata 15.

Best,

[Maarten Buis]

clear
input str1 ticker int    Time str2 streturn
"A"     3     "A1"
"A"     4     "A2"
"A"     5     "A3"
"A"     6     "A4"
"A"     7     "A5"
"A"     8     "A6"
"A"     9     "A7"
"B"     1     "B1"
"B"     2     "B2"
"B"     3     "B3"
"B"     4     "B4"
"B"     5     "B5"
"B"     6     "B6"
"C"     5     "C1"
"C"     6     "C2"
"C"     7     "C3"
"C"     8     "C4"
"C"     9     "C5"
"C"     10     "C6"
end
list, sepby(ticker)

reshape wide streturn, i(Time) j(ticker) string
list

[John Zoey]

clear
input str1 ticker int Time str2 streturn
"A" 3 "A1"
"A" 4 "A2"
"A" 5 "A3"
"A" 6 "A4"
"A" 7 "A5"
"A" 8 "A6"
"A" 9 "A7"
"B" 1 "B1"
"B" 2 "B2"
"B" 3 "B3"
"B" 4 "B4"
"B" 5 "B5"
"B" 6 "B6"
"C" 5 "C1"
"C" 6 "C2"
"C" 7 "C3"
"C" 8 "C4"
"C" 9 "C5"
"C" 10 "C6"
end
list, sepby(ticker)

reshape wide streturn, i(Time) j(ticker) string
list

Thank you! The code you provided works flawlessly.