Making variables out of observations

[Nick Bertel]

Hi,

this is my data:

* Example generated by -dataex-. For more info, type help dataex
clear
input str11 _varname float v1
"mean__b_Age"  -.17356347
"sd__b_Age"   .0043046745
"min__b_Age"    -.1809183
"max__b_Age"   -.16578566
"mean__b_ROA"    3.140546
"sd__b_ROA"     .06006654
"min__b_ROA"     3.058667
"max__b_ROA"    3.2449396
end

What i would want is to have one observation for Age, with the variables mean, sd, min and max and one observation for ROA with the same variables. Any standardized way of doing that since the first column is in string format? do i have to manually set a ID for Age, ROA to do it properly?

Thank you!

[Nick Cox]

* Example generated by -dataex-. For more info, type help dataex
clear
input str11 _varname float v1
"mean__b_Age"  -.17356347
"sd__b_Age"   .0043046745
"min__b_Age"    -.1809183
"max__b_Age"   -.16578566
"mean__b_ROA"    3.140546
"sd__b_ROA"     .06006654
"min__b_ROA"     3.058667
"max__b_ROA"    3.2449396
end

gen id = 1 
reshape wide v1, i(id) j(_varname) string 
rename (v1*) (*) 

list 

     +-----------------------------------------------------------------------------------------------+
     | id   max__b_~e   max__b~A   mean__b~e   mean__~A   min__b_~e   min__b~A   sd__b_~e   sd__b_~A |
     |-----------------------------------------------------------------------------------------------|
  1. |  1   -.1657857    3.24494   -.1735635   3.140546   -.1809183   3.058667   .0043047   .0600665 |
     +-----------------------------------------------------------------------------------------------+

[Nick Bertel]

Hi Nick, Thanks for answering!
Maybe it wasn't clear enough but what i would want to archieve is that i have one observation for Age and one observation for ROA. Then for each the variables: min, max, mean, sd. Just discoverd the table feature, so i'd want something like this:

	Min	Max	sd	Mean
Age
ROA

[Nick Cox]

You did say that. It's my fault: I didn't read your question carefully enough. It yields to the same kind of trickery.

gen id = substr(_varname, -4, 4)
replace _varname = subinstr(_varname, id, "", 1)

reshape wide v1, i(id) j(_varname) string
rename (v1*) (*)
replace id = substr(id, 2, .)

[Nick Bertel]

Hi thank you Nick and sry for coming back to you so late.

Is it maybe possible to do this a little more systematic?

Like for example: max_b_ROA. Making a command so that it takes the first letters UNTIL the first "_" to get "max" and for the the other variable take all the letters AFTER the last "_" to get "ROA"
Because in my real data i have variables with varying amounts of letters and other stuff in between. The system i explained above is the only thing systematic about it. Is that possible or way to specific?

[Hemanshu Kumar]

You could do

gen varpos = strrpos(_varname, "_") + 1
gen id = substr(_varname, varpos, .)
gen __pos = strpos(_varname, "__")
gen stat = substr(_varname, 1, __pos-1)
drop _varname varpos __pos

reshape wide v1, i(id) j(stat) string
rename (v1*) (*)

which produces:

. list, noobs

  +----------------------------------------------------+
  |  id         max        mean         min         sd |
  |----------------------------------------------------|
  | Age   -.1657857   -.1735635   -.1809183   .0043047 |
  | ROA     3.24494    3.140546    3.058667   .0600665 |
  +----------------------------------------------------+