Create variable depending on many variables

[Milena Schmidt]

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

[Milena Schmidt]

It worked, thank you very much Andrew!

[Andrew Musau]

* 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.:

* 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

[Milena Schmidt]

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

* 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

[Nick Cox]

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.