Announcement

Collapse
No announcement yet.
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Create variable depending on many variables

    Hey stata community,

    I have a question regarding creating a new variable depending on many other variables.

    My dataset looks like this: (You see, there are different products depending on the country, and different sales. Some products are not (yet) on the market (sales=0)).
    Country Product Sales 1/2003 Sales 2/2003 Sales 3/2003 Sales 4/2003 Sales 1/2004 Sales 2/2004 Sales 3/2004 Sales 4/2004
    Germany Product A 0 0 0 0 0 3 5 7
    Germany Product B 0 3 4 0 0 7 8 9
    Germany Product C 12 12 12 14 15 17 18 17
    Germany Product D 2 5 7 0 0 10 8 7
    UK Product A 0 0 0 0 0 2 2 2
    UK Product B 0 0 0 2 3 4 5 6
    UK Product D 1 1 1 1 1 2 2 2
    France Product A 3 4 5 6 6 6 6 7
    France Product C 0 0 0 0 1 1 0 0
    France Product D 12 13 19 19 34 0 0 0
    Spain Product A 1 2 3 4 0 0 7 8
    Spain Product B 0 0 0 0 0 1 2 3
    Spain Product C 3 4 5 6 7 7 6 0
    Spain Product D 0 0 0 0 0 1 2 3
    What I need now is a new variable, where it says if a product is on the market 1 year after the product launch, e.g. in Germany Product B entered the market in 2/2003, and has been 4 quarters on the market (sales >0) and is still on the market one year later (in 2/2004). If the product has been at least one year on the market (all 4 quarters are sales >0), I want the variable to say 1, if not, then 0.

    Many thanks for your answers!
    Best, Milena

  • #2
    Please use dataex to show what your data look like in Stata. The column headers in your examples could not be legal variable names. There is advice on how to do this at FAQ Advice #12.

    Comment


    • #3
      Thanks Nick, I tried it, I hope it works. Here is my data set.

      Code:
      * Example generated by -dataex-. To install: ssc install dataex
      clear
      input str7 Country str9 Product byte(Sales_1_2003 Sales_2_2003 Sales_3_2003 Sales_4_2003 Sales_1_2004 Sales_2_2004 Sales_3_2004 Sales_4_2004)
      "Germany" "Product A"  0  0  0  0  0  3  5  7
      "Germany" "Product B"  0  3  4  0  0  7  8  9
      "Germany" "Product C" 12 12 12 14 15 17 18 17
      "Germany" "Product D"  2  5  7  0  0 10  8  7
      "UK"      "Product A"  0  0  0  0  0  2  2  2
      "UK"      "Product B"  0  0  0  2  3  4  5  6
      "UK"      "Product D"  1  1  1  1  1  2  2  2
      "France"  "Product A"  3  4  5  6  6  6  6  7
      "France"  "Product C"  0  0  0  0  1  1  0  0
      "France"  "Product D" 12 13 19 19 34  0  0  0
      "Spain"   "Product A"  1  2  3  4  0  0  7  8
      "Spain"   "Product B"  0  0  0  0  0  1  2  3
      "Spain"   "Product C"  3  4  5  6  7  7  6  0
      "Spain"   "Product D"  0  0  0  0  0  1  2  3
      end

      Comment


      • #4
        Code:
        * Example generated by -dataex-. To install: ssc install dataex
        clear
        input str7 Country str9 Product byte(Sales_1_2003 Sales_2_2003 Sales_3_2003 Sales_4_2003 Sales_1_2004 Sales_2_2004 Sales_3_2004 Sales_4_2004)
        "Germany" "Product A"  0  0  0  0  0  3  5  7
        "Germany" "Product B"  0  3  4  0  0  7  8  9
        "Germany" "Product C" 12 12 12 14 15 17 18 17
        "Germany" "Product D"  2  5  7  0  0 10  8  7
        "UK"      "Product A"  0  0  0  0  0  2  2  2
        "UK"      "Product B"  0  0  0  2  3  4  5  6
        "UK"      "Product D"  1  1  1  1  1  2  2  2
        "France"  "Product A"  3  4  5  6  6  6  6  7
        "France"  "Product C"  0  0  0  0  1  1  0  0
        "France"  "Product D" 12 13 19 19 34  0  0  0
        "Spain"   "Product A"  1  2  3  4  0  0  7  8
        "Spain"   "Product B"  0  0  0  0  0  1  2  3
        "Spain"   "Product C"  3  4  5  6  7  7  6  0
        "Spain"   "Product D"  0  0  0  0  0  1  2  3
        end
        
        qui ds Sales_*
        local varnames `r(varlist)'
        rename Sales_* Sales#, addnumber(1)
        reshape long Sales, i(Country Product) j(quarter)
        bys Country Product (quarter): gen inmarket= sum(sum(Sales>0 & Sales<.))==1
        bys Country Product (quarter): replace inmarket= inmarket[_n-1]+ 1 if inmarket[_n-1]> 0 & _n>1
        bys Country Product (quarter): egen wanted= max(inmarket>4 & Sales>0 & Sales<.)
        drop inmarket
        reshape wide Sales, i(Country Product) j(quarter)
        rename Sales* (`varnames')
        Res.:

        Code:
        * Example generated by -dataex-. To install: ssc install dataex
        clear
        input str7 Country str9 Product byte(Sales_1_2003 Sales_2_2003 Sales_3_2003 Sales_4_2003 Sales_1_2004 Sales_2_2004 Sales_3_2004 Sales_4_2004) float wanted
        "France"  "Product A"  3  4  5  6  6  6  6  7 1
        "France"  "Product C"  0  0  0  0  1  1  0  0 0
        "France"  "Product D" 12 13 19 19 34  0  0  0 1
        "Germany" "Product A"  0  0  0  0  0  3  5  7 0
        "Germany" "Product B"  0  3  4  0  0  7  8  9 1
        "Germany" "Product C" 12 12 12 14 15 17 18 17 1
        "Germany" "Product D"  2  5  7  0  0 10  8  7 1
        "Spain"   "Product A"  1  2  3  4  0  0  7  8 1
        "Spain"   "Product B"  0  0  0  0  0  1  2  3 0
        "Spain"   "Product C"  3  4  5  6  7  7  6  0 1
        "Spain"   "Product D"  0  0  0  0  0  1  2  3 0
        "UK"      "Product A"  0  0  0  0  0  2  2  2 0
        "UK"      "Product B"  0  0  0  2  3  4  5  6 1
        "UK"      "Product D"  1  1  1  1  1  2  2  2 1
        end
        Last edited by Andrew Musau; 05 Mar 2021, 08:08.

        Comment


        • #5
          It worked, thank you very much Andrew!

          Comment

          Working...
          X