changing a column to variable names and one to values

[Collin James]

Hi all,

My data looks like the below and is at the individual level, wide (I want it to be wide).

var_name1 var_value1 var_name2 var_value2
pri_housing 0 pri_homeless 1
pri_housing 1 pri_homeless 1

I would like to make it such that the columns are actually the var_names (pri_housing, pri_homeless) and then the values in those columns are 0 or 1.
So more like

pri_housing pri_homeless
0 1
1 1

Suggestions on this? Thanks!

[Clyde Schechter]

To clarify, is the value of var_name1 "pre_housing", and that of var_name2 "pri_homeless" in all observations, or do they sometimes take on other values?

[Collin James]

It is the same in all observations!

[Clyde Schechter]

* Example generated by -dataex-. For more info, type help dataex
clear
input str11 var_name1 byte var_value1 str12 var_name2 byte var_value2
"pri_housing" 0 "pri_homeless" 1
"pri_housing" 1 "pri_homeless" 1
end

foreach v of varlist var_value* {
    local namer: subinstr local v "var_value" "var_name"
    rename `v' `=`namer'[1]'
    drop `namer'
}

In the future, when showing data examples, please use the -dataex- command to do so, as I have here. If you are running version 18, 17, 16 or a fully updated version 15.1 or 14.2, -dataex- is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.

[Collin James]

Ah, here you go:

I am trying to do the following now :reshape wide variable_value variable_id run_dt, i(mci run_dt) j(variable_name) string

But, it tells me "variable_valuePRI_BH_ASSMNT_COUNT_1_OA invalid variable name"

What do you think? I thought I could do it in my reshape wide but something isn't working.
----------------------- copy starting from the next line -----------------------

* Example generated by -dataex-. For more info, type help dataex
clear
input long(mci_uniq_id variable_id) str53 variable_name str11 variable_value double run_dt
97 40018 "PRI_GUN_CNI_NO_INTERNET_RATE_20215YE"                  "20"        1.7672256e+12
97   740 "PRI_HL_CRT_ALL_DUMMY_EVER"                             "0"         1.7672256e+12
97   757 "PRI_HL_CRT_CM_PLS_CRM_COUNT_1"                         "0"         1.7672256e+12

end
format %tc run_dt

[Clyde Schechter]

Yes, "variable_valuePRI_BH_ASSMNT_COUNT_1_OA" is invalid because it is too long. This is the name of a variable you are asking -reshape- to create. It can't because the maximum allowable length for any variable name is 32 characters. So you're going to have to give the value in variable_name a "haircut" and shorten them so that when combined with variable_value, variable_id, and run_dt,the total comes to at most 32 characters. The longest of these prefixes is variable_value, with 14 characters So you will have to shorten all the values of variable_name to 18 characters or less. Alternatively, you could rename variable_id, run_dt, and variable_value to something shorter, so you can save some room for the contents of variable_name. For example, variable_id could be var_id, or maybe even just id. variable_value, could similarly be var_val, or even just plain val.

I'm not going to propose ways to shorten variable_name's values. They apparently have many parts, some abbreviated or coded and not, to me, comprehensible. And I have no idea what can be disposed of or modified without losing important information.