Generating values for columns between a range
1
I have the following dataset
A B begin_yr end_yr
asset brown 2007 2010
asset blue 2008 2008
basics caramel 2015 2015
cows dork 2004 2006
I want A and B to have rows for each year represented.
I expanded for each year:
gen x = end_yr - begin_yr
expand x +1
This gives me the following:
A B begin_yr end_yr x
asset brown 2007 2010 3
asset brown 2007 2010 3
asset brown 2007 2010 3
asset brown 2007 2010 3
asset blue 2008 2008 0
basics caramel 2015 2015 0
cows dork 2004 2006 2
Ultimately, I want the following dataset:
A B begin_yr end_yr x year
asset brown 2007 2010 3 2007
asset brown 2007 2010 3 2008
asset brown 2007 2010 3 2009
asset brown 2007 2010 3 2010
asset blue 2008 2008 0 2008
basics caramel 2015 2015 0 2015
cows dork 2004 2006 2 2004
cows dork 2004 2006 2 2005
cows dork 2004 2006 2 2006
This is what I have so far:
gen year = begin_yr if begin_yr!=end_yr
How do I populate the rest of the variable year?
stata stata-macros
edited Nov 20 '18 at 19:40
Pearly Spencer
11k173461
asked Nov 20 '18 at 18:54
Jyothsna HarithsaJyothsna Harithsa
445
add a comment |
1
I have the following dataset
A B begin_yr end_yr
asset brown 2007 2010
asset blue 2008 2008
basics caramel 2015 2015
cows dork 2004 2006
I want A and B to have rows for each year represented.
I expanded for each year:
gen x = end_yr - begin_yr
expand x +1
This gives me the following:
A B begin_yr end_yr x
asset brown 2007 2010 3
asset brown 2007 2010 3
asset brown 2007 2010 3
asset brown 2007 2010 3
asset blue 2008 2008 0
basics caramel 2015 2015 0
cows dork 2004 2006 2
Ultimately, I want the following dataset:
A B begin_yr end_yr x year
asset brown 2007 2010 3 2007
asset brown 2007 2010 3 2008
asset brown 2007 2010 3 2009
asset brown 2007 2010 3 2010
asset blue 2008 2008 0 2008
basics caramel 2015 2015 0 2015
cows dork 2004 2006 2 2004
cows dork 2004 2006 2 2005
cows dork 2004 2006 2 2006
This is what I have so far:
gen year = begin_yr if begin_yr!=end_yr
How do I populate the rest of the variable year?
stata stata-macros
edited Nov 20 '18 at 19:40
Pearly Spencer
11k173461
asked Nov 20 '18 at 18:54
Jyothsna HarithsaJyothsna Harithsa
445
add a comment |
1
1
1
I have the following dataset
A B begin_yr end_yr
asset brown 2007 2010
asset blue 2008 2008
basics caramel 2015 2015
cows dork 2004 2006
I want A and B to have rows for each year represented.
I expanded for each year:
gen x = end_yr - begin_yr
expand x +1
This gives me the following:
A B begin_yr end_yr x
asset brown 2007 2010 3
asset brown 2007 2010 3
asset brown 2007 2010 3
asset brown 2007 2010 3
asset blue 2008 2008 0
basics caramel 2015 2015 0
cows dork 2004 2006 2
Ultimately, I want the following dataset:
A B begin_yr end_yr x year
asset brown 2007 2010 3 2007
asset brown 2007 2010 3 2008
asset brown 2007 2010 3 2009
asset brown 2007 2010 3 2010
asset blue 2008 2008 0 2008
basics caramel 2015 2015 0 2015
cows dork 2004 2006 2 2004
cows dork 2004 2006 2 2005
cows dork 2004 2006 2 2006
This is what I have so far:
gen year = begin_yr if begin_yr!=end_yr
How do I populate the rest of the variable year?
stata stata-macros
edited Nov 20 '18 at 19:40
Pearly Spencer
11k173461
asked Nov 20 '18 at 18:54
Jyothsna HarithsaJyothsna Harithsa
445
I have the following dataset
A B begin_yr end_yr
asset brown 2007 2010
asset blue 2008 2008
basics caramel 2015 2015
cows dork 2004 2006
I want A and B to have rows for each year represented.
I expanded for each year:
gen x = end_yr - begin_yr
expand x +1
This gives me the following:
A B begin_yr end_yr x
asset brown 2007 2010 3
asset brown 2007 2010 3
asset brown 2007 2010 3
asset brown 2007 2010 3
asset blue 2008 2008 0
basics caramel 2015 2015 0
cows dork 2004 2006 2
Ultimately, I want the following dataset:
A B begin_yr end_yr x year
asset brown 2007 2010 3 2007
asset brown 2007 2010 3 2008
asset brown 2007 2010 3 2009
asset brown 2007 2010 3 2010
asset blue 2008 2008 0 2008
basics caramel 2015 2015 0 2015
cows dork 2004 2006 2 2004
cows dork 2004 2006 2 2005
cows dork 2004 2006 2 2006
This is what I have so far:
gen year = begin_yr if begin_yr!=end_yr
How do I populate the rest of the variable year?
stata stata-macros
stata stata-macros
edited Nov 20 '18 at 19:40
Pearly Spencer
11k173461
asked Nov 20 '18 at 18:54
Jyothsna HarithsaJyothsna Harithsa
445
edited Nov 20 '18 at 19:40
Pearly Spencer
11k173461
asked Nov 20 '18 at 18:54
Jyothsna HarithsaJyothsna Harithsa
445
edited Nov 20 '18 at 19:40
Pearly Spencer
11k173461
edited Nov 20 '18 at 19:40
Pearly Spencer
11k173461
edited Nov 20 '18 at 19:40
Pearly Spencer
11k173461
11k173461
asked Nov 20 '18 at 18:54
Jyothsna HarithsaJyothsna Harithsa
445
asked Nov 20 '18 at 18:54
Jyothsna HarithsaJyothsna Harithsa
445
asked Nov 20 '18 at 18:54
Jyothsna HarithsaJyothsna Harithsa
445
445
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
3
Here's a twist building on @Pearly Spencer's code:
clear
input strL A strL B begin_yr end_yr
asset brown 2007 2010
basics caramel 2015 2015
cows dork 2004 2006
end
gen toexpand = end - begin + 1
expand toexpand
bysort A : gen year = begin + _n - 1
list, sepby(A)
+--------------------------------------------------------+
| A B begin_yr end_yr toexpand year |
|--------------------------------------------------------|
1. | asset brown 2007 2010 4 2007 |
2. | asset brown 2007 2010 4 2008 |
3. | asset brown 2007 2010 4 2009 |
4. | asset brown 2007 2010 4 2010 |
|--------------------------------------------------------|
5. | basics caramel 2015 2015 1 2015 |
|--------------------------------------------------------|
6. | cows dork 2004 2006 3 2004 |
7. | cows dork 2004 2006 3 2005 |
8. | cows dork 2004 2006 3 2006 |
+--------------------------------------------------------+
Nothing against tsset or tsfill but neither is needed for this.
answered Nov 20 '18 at 19:21
Nick CoxNick Cox
25.2k42038
add a comment |
2
The following works for me:
clear
input strL A strL B begin_yr end_yr
asset brown 2007 2010
basics caramel 2015 2015
cows dork 2004 2006
end
generate id = _n
expand 2
clonevar year = begin_yr
bysort id: replace year = end_yr[2] if _n == _N
drop if _n == 3
tsset id year
tsfill
foreach var in A B begin_yr end_yr {
bysort id: replace `var' = `var'[1]
}
list
+--------------------------------------------------+
| A B begin_yr end_yr id year |
|--------------------------------------------------|
1. | asset brown 2007 2010 1 2007 |
2. | asset brown 2007 2010 1 2008 |
3. | asset brown 2007 2010 1 2009 |
4. | asset brown 2007 2010 1 2010 |
5. | basics caramel 2015 2015 2 2015 |
|--------------------------------------------------|
6. | cows dork 2004 2006 3 2004 |
7. | cows dork 2004 2006 3 2005 |
8. | cows dork 2004 2006 3 2006 |
+--------------------------------------------------+
answered Nov 20 '18 at 19:10
Pearly SpencerPearly Spencer
11k173461
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53399710%2fgenerating-values-for-columns-between-a-range%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
3
Here's a twist building on @Pearly Spencer's code:
clear
input strL A strL B begin_yr end_yr
asset brown 2007 2010
basics caramel 2015 2015
cows dork 2004 2006
end
gen toexpand = end - begin + 1
expand toexpand
bysort A : gen year = begin + _n - 1
list, sepby(A)
+--------------------------------------------------------+
| A B begin_yr end_yr toexpand year |
|--------------------------------------------------------|
1. | asset brown 2007 2010 4 2007 |
2. | asset brown 2007 2010 4 2008 |
3. | asset brown 2007 2010 4 2009 |
4. | asset brown 2007 2010 4 2010 |
|--------------------------------------------------------|
5. | basics caramel 2015 2015 1 2015 |
|--------------------------------------------------------|
6. | cows dork 2004 2006 3 2004 |
7. | cows dork 2004 2006 3 2005 |
8. | cows dork 2004 2006 3 2006 |
+--------------------------------------------------------+
Nothing against tsset or tsfill but neither is needed for this.
answered Nov 20 '18 at 19:21
Nick CoxNick Cox
25.2k42038
add a comment |
3
Here's a twist building on @Pearly Spencer's code:
clear
input strL A strL B begin_yr end_yr
asset brown 2007 2010
basics caramel 2015 2015
cows dork 2004 2006
end
gen toexpand = end - begin + 1
expand toexpand
bysort A : gen year = begin + _n - 1
list, sepby(A)
+--------------------------------------------------------+
| A B begin_yr end_yr toexpand year |
|--------------------------------------------------------|
1. | asset brown 2007 2010 4 2007 |
2. | asset brown 2007 2010 4 2008 |
3. | asset brown 2007 2010 4 2009 |
4. | asset brown 2007 2010 4 2010 |
|--------------------------------------------------------|
5. | basics caramel 2015 2015 1 2015 |
|--------------------------------------------------------|
6. | cows dork 2004 2006 3 2004 |
7. | cows dork 2004 2006 3 2005 |
8. | cows dork 2004 2006 3 2006 |
+--------------------------------------------------------+
Nothing against tsset or tsfill but neither is needed for this.
answered Nov 20 '18 at 19:21
Nick CoxNick Cox
25.2k42038
add a comment |
3
3
3
Here's a twist building on @Pearly Spencer's code:
clear
input strL A strL B begin_yr end_yr
asset brown 2007 2010
basics caramel 2015 2015
cows dork 2004 2006
end
gen toexpand = end - begin + 1
expand toexpand
bysort A : gen year = begin + _n - 1
list, sepby(A)
+--------------------------------------------------------+
| A B begin_yr end_yr toexpand year |
|--------------------------------------------------------|
1. | asset brown 2007 2010 4 2007 |
2. | asset brown 2007 2010 4 2008 |
3. | asset brown 2007 2010 4 2009 |
4. | asset brown 2007 2010 4 2010 |
|--------------------------------------------------------|
5. | basics caramel 2015 2015 1 2015 |
|--------------------------------------------------------|
6. | cows dork 2004 2006 3 2004 |
7. | cows dork 2004 2006 3 2005 |
8. | cows dork 2004 2006 3 2006 |
+--------------------------------------------------------+
Nothing against tsset or tsfill but neither is needed for this.
answered Nov 20 '18 at 19:21
Nick CoxNick Cox
25.2k42038
Here's a twist building on @Pearly Spencer's code:
clear
input strL A strL B begin_yr end_yr
asset brown 2007 2010
basics caramel 2015 2015
cows dork 2004 2006
end
gen toexpand = end - begin + 1
expand toexpand
bysort A : gen year = begin + _n - 1
list, sepby(A)
+--------------------------------------------------------+
| A B begin_yr end_yr toexpand year |
|--------------------------------------------------------|
1. | asset brown 2007 2010 4 2007 |
2. | asset brown 2007 2010 4 2008 |
3. | asset brown 2007 2010 4 2009 |
4. | asset brown 2007 2010 4 2010 |
|--------------------------------------------------------|
5. | basics caramel 2015 2015 1 2015 |
|--------------------------------------------------------|
6. | cows dork 2004 2006 3 2004 |
7. | cows dork 2004 2006 3 2005 |
8. | cows dork 2004 2006 3 2006 |
+--------------------------------------------------------+
Nothing against tsset or tsfill but neither is needed for this.
answered Nov 20 '18 at 19:21
Nick CoxNick Cox
25.2k42038
answered Nov 20 '18 at 19:21
Nick CoxNick Cox
25.2k42038
answered Nov 20 '18 at 19:21
Nick CoxNick Cox
25.2k42038
answered Nov 20 '18 at 19:21
Nick CoxNick Cox
25.2k42038
25.2k42038
add a comment |
add a comment |
2
The following works for me:
clear
input strL A strL B begin_yr end_yr
asset brown 2007 2010
basics caramel 2015 2015
cows dork 2004 2006
end
generate id = _n
expand 2
clonevar year = begin_yr
bysort id: replace year = end_yr[2] if _n == _N
drop if _n == 3
tsset id year
tsfill
foreach var in A B begin_yr end_yr {
bysort id: replace `var' = `var'[1]
}
list
+--------------------------------------------------+
| A B begin_yr end_yr id year |
|--------------------------------------------------|
1. | asset brown 2007 2010 1 2007 |
2. | asset brown 2007 2010 1 2008 |
3. | asset brown 2007 2010 1 2009 |
4. | asset brown 2007 2010 1 2010 |
5. | basics caramel 2015 2015 2 2015 |
|--------------------------------------------------|
6. | cows dork 2004 2006 3 2004 |
7. | cows dork 2004 2006 3 2005 |
8. | cows dork 2004 2006 3 2006 |
+--------------------------------------------------+
answered Nov 20 '18 at 19:10
Pearly SpencerPearly Spencer
11k173461
add a comment |
2
The following works for me:
clear
input strL A strL B begin_yr end_yr
asset brown 2007 2010
basics caramel 2015 2015
cows dork 2004 2006
end
generate id = _n
expand 2
clonevar year = begin_yr
bysort id: replace year = end_yr[2] if _n == _N
drop if _n == 3
tsset id year
tsfill
foreach var in A B begin_yr end_yr {
bysort id: replace `var' = `var'[1]
}
list
+--------------------------------------------------+
| A B begin_yr end_yr id year |
|--------------------------------------------------|
1. | asset brown 2007 2010 1 2007 |
2. | asset brown 2007 2010 1 2008 |
3. | asset brown 2007 2010 1 2009 |
4. | asset brown 2007 2010 1 2010 |
5. | basics caramel 2015 2015 2 2015 |
|--------------------------------------------------|
6. | cows dork 2004 2006 3 2004 |
7. | cows dork 2004 2006 3 2005 |
8. | cows dork 2004 2006 3 2006 |
+--------------------------------------------------+
answered Nov 20 '18 at 19:10
Pearly SpencerPearly Spencer
11k173461
add a comment |
2
2
2
The following works for me:
clear
input strL A strL B begin_yr end_yr
asset brown 2007 2010
basics caramel 2015 2015
cows dork 2004 2006
end
generate id = _n
expand 2
clonevar year = begin_yr
bysort id: replace year = end_yr[2] if _n == _N
drop if _n == 3
tsset id year
tsfill
foreach var in A B begin_yr end_yr {
bysort id: replace `var' = `var'[1]
}
list
+--------------------------------------------------+
| A B begin_yr end_yr id year |
|--------------------------------------------------|
1. | asset brown 2007 2010 1 2007 |
2. | asset brown 2007 2010 1 2008 |
3. | asset brown 2007 2010 1 2009 |
4. | asset brown 2007 2010 1 2010 |
5. | basics caramel 2015 2015 2 2015 |
|--------------------------------------------------|
6. | cows dork 2004 2006 3 2004 |
7. | cows dork 2004 2006 3 2005 |
8. | cows dork 2004 2006 3 2006 |
+--------------------------------------------------+
answered Nov 20 '18 at 19:10
Pearly SpencerPearly Spencer
11k173461
The following works for me:
clear
input strL A strL B begin_yr end_yr
asset brown 2007 2010
basics caramel 2015 2015
cows dork 2004 2006
end
generate id = _n
expand 2
clonevar year = begin_yr
bysort id: replace year = end_yr[2] if _n == _N
drop if _n == 3
tsset id year
tsfill
foreach var in A B begin_yr end_yr {
bysort id: replace `var' = `var'[1]
}
list
+--------------------------------------------------+
| A B begin_yr end_yr id year |
|--------------------------------------------------|
1. | asset brown 2007 2010 1 2007 |
2. | asset brown 2007 2010 1 2008 |
3. | asset brown 2007 2010 1 2009 |
4. | asset brown 2007 2010 1 2010 |
5. | basics caramel 2015 2015 2 2015 |
|--------------------------------------------------|
6. | cows dork 2004 2006 3 2004 |
7. | cows dork 2004 2006 3 2005 |
8. | cows dork 2004 2006 3 2006 |
+--------------------------------------------------+
answered Nov 20 '18 at 19:10
Pearly SpencerPearly Spencer
11k173461
edited Nov 20 '18 at 19:16
answered Nov 20 '18 at 19:10
Pearly SpencerPearly Spencer
11k173461
answered Nov 20 '18 at 19:10
Pearly SpencerPearly Spencer
11k173461
answered Nov 20 '18 at 19:10
Pearly SpencerPearly Spencer
11k173461
11k173461
add a comment |
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53399710%2fgenerating-values-for-columns-between-a-range%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
