Spliting variables into rows

[Kshitij Dhiman]

I have a dataset in STATA which looks as follows

ID	Product	Region	Sales
001	JSF045	East~West~North	45~78~14
002	JSF056	East~West	56~35
003	GWW1	West~North~South~South East	11~16~45~36

I want to split the Region and Sales variables into separate rows (separation at ~). Each row will have a different number of regions and sales figures but for every row, a region will always correspond to a sales figure.

I want the final data to look like as below

ID	Product	Region	Sales
001	JSF045	East	45
001	JSF045	West	78
001	JSF045	North	14
002	JSF056	East	56
002	JSF056	West	35

I am new to using STATA so any help will be very useful.

[Nick Cox]

Cross-posted and answered at https://stackoverflow.com/questions/...eral-variables

Please read the FAQ before posting https://www.statalist.org/forums/help and

tell us about cross-posting (#8)

and read all the way through to #18.

[Andrew Musau]

* Example generated by -dataex-. For more info, type help dataex
clear
input str3 id str6 product str27 region str11 sales
"001" "JSF045" "East~West~North"             "45~78~14"   
"002" "JSF056" "East~West"                   "56~35"      
"003" "GWW1"   "West~North~South~South East" "11~16~45~36"
end

gen toexpand= length(sales)- length(subinstr(sales,"~", "", .))+1
expand toexpand
foreach var in region sales{
    bys id product: replace `var'= subinstr(word(subinstr(subinstr(trim(`var'), " ", "_", .), "~", " ", .), _n), "_", " ", .)
}

Res.:

. l, sepby(id prod)

     +-----------------------------------------------+
     |  id   product       region   sales   toexpand |
     |-----------------------------------------------|
  1. | 001    JSF045         East      45          3 |
  2. | 001    JSF045         West      78          3 |
  3. | 001    JSF045        North      14          3 |
     |-----------------------------------------------|
  4. | 002    JSF056         East      56          2 |
  5. | 002    JSF056         West      35          2 |
     |-----------------------------------------------|
  6. | 003      GWW1         West      11          4 |
  7. | 003      GWW1        North      16          4 |
  8. | 003      GWW1        South      45          4 |
  9. | 003      GWW1   South East      36          4 |
     +-----------------------------------------------+