Creating New Variable based on Values of Another Variable (by group)

[Carlos Sanchez]

Hello Stata Friends:

I would like to make the values of one variable, each into their own new variables themselves to be applied to everyone in a given group (in this case, state-year pairs).

I currently have:

I am trying to get:

Any help on this would be greatly appreciated!

Thank you!
Carlos

[Clyde Schechter]

frame put yearstate, into(receiving)
drop if missing(var)
duplicates drop
by yearstate (var), sort gen _j = _n
reshape wide var, i(yearstate) j(_j)

frame change receiving
frlink m:1 yearstate, frame(default)
frget var*, from(default)

Caveat: Code not tested because data was not provide in a usable format.

In the future, when showing data examples, please use the -dataex- command to do so. If you are running version 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.

[Nick Cox]

Here's another way to do it in place.

I have not tested @Clyde Schechter's code but note that

by yearstate (var), sort gen _j = _n

is a typo for

  
 by yearstate (var), sort: gen _j = _n

The data example (do note Clyde's comments!) does not include duplicate observations, but the code does not depend on their being absent.

The code would work if yearstate were a numeric variable with value labels. However, sooner or later separate variables for state and year will be a good idea if you don't have them already.

Also, just in case it's not necessarily true that each state-year combination sports the same number of distinct values, I have shown code that works more generally. I think Clyde's code should work that way too.

* Example generated by -dataex-. For more info, type help dataex
clear
input str6 yearstate float var
"1996TX" 140
"1996TX" 412
"1996TX" 692
"1996UT" 182
"1996UT" 546
"1996UT" 637
"1996UT" 999
"1996TX"   .
"1996UT"   .
end 

bysort yearstate (var) : gen tag = sum((var != var[_n-1]) & var < .) 
by yearstate : replace tag = . if tag == tag[_n-1]
su tag, meanonly 

forval j = 1/`r(max)' { 
    egen count = total(tag == `j'), by(yearstate)
    egen var`j' = total((tag == `j') * var) if count, by(yearstate)
    drop count 
}

list, sepby(yearstate)

     +--------------------------------------------------+
     | yearst~e   var   tag   var1   var2   var3   var4 |
     |--------------------------------------------------|
  1. |   1996TX   140     1    140    412    692      . |
  2. |   1996TX   412     2    140    412    692      . |
  3. |   1996TX   692     3    140    412    692      . |
  4. |   1996TX     .     .    140    412    692      . |
     |--------------------------------------------------|
  5. |   1996UT   182     1    182    546    637    999 |
  6. |   1996UT   546     2    182    546    637    999 |
  7. |   1996UT   637     3    182    546    637    999 |
  8. |   1996UT   999     4    182    546    637    999 |
  9. |   1996UT     .     .    182    546    637    999 |
     +--------------------------------------------------+

See also https://www.stata-journal.com/articl...article=dm0055