Reshaping Dataset

[Drew Nelson]

Hello,

Here's an example of my data set:

Account Jan21 Feb21 Mar21
Income 500 1000 750
Expense 700 400 250
Profit 200 600 500

I would like to reshape these data so Jan21, Feb21, & Mar21 become rows in a variable called, Date. I would then like to take the rows of the Account variable and turn them into columns. For example:

Date Income Expense Profit
Jan21 500 700 200
Feb21 1000 400 600
Mar21 750 250 500


I've tried xpose which works but it deletes all of the info in the Account variable. I've been attempting to reshape and try a few suggestions i've found onine, but thus far, no luck.

Any help would be much appreciated.

Thank you!

[Ken Chui]

Try add the "varname" option:

xpose, varname clear

[Drew Nelson]

Unfortunately, it deletes the account var because its a string

[Ken Chui]

Unfortunately, it deletes the account var because its a string

Then maybe double reshape:

clear
input str15 Account Jan21 Feb21 Mar21
Income 500 1000 750
Expense 700 400 250
Profit 200 600 500
end

foreach x of varlist Jan21-Mar21{
    rename `x' y_`x'
}

reshape long y_, i(Account) j(date, string)

reshape wide y_, i(date) j(Account, string)
foreach x of varlist y_Expense-y_Profit{
    local x2 = substr("`x'", 3, 25)
    rename `x' `x2'
}

Results:

     +-----------------------------------+
     |  date   Expense   Income   Profit |
     |-----------------------------------|
  1. | Feb21       400     1000      600 |
  2. | Jan21       700      500      200 |
  3. | Mar21       250      750      500 |
     +-----------------------------------+

[Drew Nelson]

Thank you!!!!