Hi,
I am using a panel dataset with different countries (example below). For each date there are two variables (value and number). I want to expand my dataset with all possible consecutive sequencies of 10 observation within ID that does not have a gap longer than 3 days, and renaming the 10 expanded observations with IDk. Where k is 1 to n expanded sequencies. I made a tag-variable for when the observations are more than 3 days apart (longgap).
Variables:
ID: country
date: date
diffdays: days since last observation
longgap: more than 3 days since last observation
value: some value
number: some number
So the expanded sequences (added at the bottom of my dataset) would be:
And so on. BUT, when there is longgap==1 and less than 10 consecutive dates before that, no expanded sequence should be added. The next sequence to be added at the bottom of the dataset should thus be the first sequence of ten dates where the maximum gap between dates are 3 or less.
To summarize. Add rolling sequencies of ten consecutive observations with maximum 3 days between observations, within ID.
Dummy code:
local k =1
some kind of loop {
bys ID: expand 10 if longgap!=1, start at observation==`k'
local k `++k'
}
I am using a panel dataset with different countries (example below). For each date there are two variables (value and number). I want to expand my dataset with all possible consecutive sequencies of 10 observation within ID that does not have a gap longer than 3 days, and renaming the 10 expanded observations with IDk. Where k is 1 to n expanded sequencies. I made a tag-variable for when the observations are more than 3 days apart (longgap).
Variables:
ID: country
date: date
diffdays: days since last observation
longgap: more than 3 days since last observation
value: some value
number: some number
Code:
* Example generated by -dataex-. To install: ssc install dataex clear input str15 ID double date float(diffdays longgap) double(value number) "Sweden" 21614 . 1 8.72 8.74 "Sweden" 21615 1 . 8.32 8.72 "Sweden" 21616 1 . 8.44 8.68 "Sweden" 21619 3 . 8.66 8.9 "Sweden" 21620 1 . 8.8 8.8 "Sweden" 21621 1 . 8.88 8.9 "Sweden" 21622 1 . 8.78 8.8 "Sweden" 21623 1 . 8.88 8.98 "Sweden" 21626 3 . 8.98 8.98 "Sweden" 21627 1 . 8.78 8.9 "Sweden" 21628 1 . 8.9 8.9 "Sweden" 21629 1 . 8.76 8.9 "Sweden" 21630 1 . 8.98 9.1 "Sweden" 21633 3 . 8.9 9.94 "Sweden" 21634 1 . 10 10.45 "Sweden" 21635 1 . 9.5 9.5 "Sweden" 21636 1 . 9.24 9.4 "Sweden" 21637 1 . 9.4 9.4 "Sweden" 21640 3 . 10.3 10.3 "Sweden" 21641 1 . 10 10.25 "Sweden" 21642 1 . 9.98 10.25 "Sweden" 21643 1 . 10.35 11 "Sweden" 21644 1 . 11 11.5 "Sweden" 21647 3 . 11 11.85 "Sweden" 21648 1 . 11.5 11.6 "Sweden" 21649 1 . 11.8 13.75 "Sweden" 21650 1 . 13.7 14.5 "Sweden" 21651 1 . 13.6 14 "Sweden" 21654 3 . 13 13.1 "Sweden" 21655 1 . 12.7 13.2 "Sweden" 21656 1 . 13.2 13.5 "Sweden" 21657 1 . 14.15 14.15 "Sweden" 21662 5 1 13.3 14.7 "Sweden" 21663 1 . 14 15 "Sweden" 21664 1 . 14.6 14.75 "Sweden" 21665 1 . 13.8 14.25 "Sweden" 21668 3 . 14 14.7 "Sweden" 21669 1 . 14 14.65 "Sweden" 21671 2 . 14.5 14.7 "Sweden" 21672 1 . 14.05 14.05 "Sweden" 21675 3 . 13.8 13.8 "Sweden" 21676 1 . 13.45 13.45 "Sweden" 21677 1 . 12.9 12.9 "Sweden" 21678 1 . 12.8 13 "Sweden" 21679 1 . 13 13.3 "Sweden" 21682 3 . 13.1 13.5 "Sweden" 21683 1 . 13.1 13.85 "Sweden" 21684 1 . 13.8 14.1 "Sweden" 21685 1 . 13.75 13.8 "Sweden" 21686 1 . 13.8 13.8 "Sweden" 21689 3 . 13.7 14 "Sweden" 21690 1 . 14.2 16.1 "Sweden" 21691 1 . 15.1 16.5 "Sweden" 21692 1 . 16.1 16.2 "Sweden" 21693 1 . 16.15 17 "Sweden" 21696 3 . 17.25 19.5 "Sweden" 21697 1 . 19.4 19.9 "Sweden" 21698 1 . 19.6 19.8 "Sweden" 21700 2 . 19.35 19.35 "Sweden" 21703 3 . 16.5 17.75 "Sweden" 21704 1 . 17 17.2 "Sweden" 21705 1 . 16.95 17.75 "Sweden" 21707 2 . 17 17.75 "Sweden" 21710 3 . 17.7 17.7 "Sweden" 21711 1 . 16.35 16.9 "Sweden" 21712 1 . 16.15 16.15 "Sweden" 21713 1 . 16.35 16.4 "Sweden" 21714 1 . 16 16.5 "Sweden" 21717 3 . 15.3 16.25 "Sweden" 21718 1 . 15.05 15.3 "Sweden" 21719 1 . 14.8 14.8 "Sweden" 21720 1 . 15.1 17.25 "Sweden" 21724 4 1 16.85 17.2 "Sweden" 21725 1 . 17.7 18.6 "Sweden" 21726 1 . 19.45 19.45 "Sweden" 21727 1 . 18.35 18.5 "Sweden" 21728 1 . 18.4 18.4 "Sweden" 21731 3 . 17.55 18.35 "Sweden" 21732 1 . 17.6 18 "Sweden" 21733 1 . 17.8 18.25 "Sweden" 21734 1 . 17.5 17.9 "Sweden" 21735 1 . 17.3 17.85 "Sweden" 21738 3 . 17.45 17.95 "Sweden" 21739 1 . 17 17.4 "Sweden" 21740 1 . 16.15 16.95 "Sweden" 21741 1 . 16.45 16.45 "Sweden" 21742 1 . 16.4 16.45 "Sweden" 21745 3 . 16.2 16.25 "Sweden" 21746 1 . 15.65 15.8 "Sweden" 21747 1 . 15.8 15.8 "Sweden" 21748 1 . 15.1 15.3 "Sweden" 21749 1 . 15 16.2 "Sweden" 21752 3 . 15.5 17 "Sweden" 21753 1 . 15.8 16.5 "Sweden" 21754 1 . 16.55 16.7 "Sweden" 21755 1 . 16.95 16.95 "Sweden" 21756 1 . 16.5 16.95 "Sweden" 21759 3 . 16.9 17 "Sweden" 21760 1 . 16.9 16.95 "Sweden" 21761 1 . 17 17.15 "Sweden" 21762 1 . 16.6 17.25 "Sweden" 21763 1 . 16 17.2 "US" 21614 . 1 13.3 13.52 "US" 21615 1 . 13 13.9 "US" 21616 1 . 13.46 13.86 "US" 21619 3 . 13.84 14.44 "US" 21620 1 . 14.8 15.4 "US" 21621 1 . 15.3 16.78 "US" 21622 1 . 15.7 15.7 "US" 21623 1 . 14.58 14.84 "US" 21626 3 . 14.3 14.88 "US" 21627 1 . 14.4 14.42 "US" 21628 1 . 14.12 14.4 "US" 21629 1 . 14.36 14.84 "US" 21630 1 . 14.76 14.76 "US" 21633 3 . 14.38 14.88 "US" 21634 1 . 14.56 14.6 "US" 21635 1 . 14.6 16.78 "US" 21636 1 . 16.3 17.72 "US" 21637 1 . 16.38 17.7 "US" 21640 3 . 15.75 17.1 "US" 21641 1 . 15.6 16.3 "US" 21642 1 . 16.05 16.15 "US" 21643 1 . 16.15 17.7 "US" 21644 1 . 16.7 17.7 "US" 21647 3 . 17.25 17.9 "US" 21648 1 . 17.95 18 "US" 21649 1 . 17.35 17.5 "US" 21650 1 . 17.3 17.3 "US" 21651 1 . 16.9 17 "US" 21654 3 . 17.35 17.35 "US" 21655 1 . 16.55 16.85 "US" 21656 1 . 16.8 16.8 "US" 21657 1 . 16.2 16.65 "US" 21662 5 1 15.8 15.8 "US" 21663 1 . 15.2 16 "US" 21664 1 . 14.55 18.4 "US" 21665 1 . 16.85 17.5 "US" 21668 3 . 17.75 18 "US" 21669 1 . 17 17.9 "US" 21671 2 . 16.4 17.3 "US" 21672 1 . 17 17.25 "US" 21675 3 . 17.25 17.25 "US" 21676 1 . 16.95 16.95 "US" 21677 1 . 17.15 17.15 "US" 21678 1 . 16.15 17.8 "US" 21679 1 . 16.8 16.8 "US" 21682 3 . 16.3 17 "US" 21683 1 . 16.25 16.8 "US" 21684 1 . 16.6 17 "US" 21685 1 . 16.5 16.9 "US" 21686 1 . 16.55 16.9 "US" 21689 3 . 16 17.45 "US" 21690 1 . 17.2 17.2 "US" 21691 1 . 17 17.1 "US" 21692 1 . 17.1 17.2 "US" 21693 1 . 16.75 16.75 "US" 21696 3 . 16.7 16.7 "US" 21697 1 . 16.05 16.6 "US" 21698 1 . 16.55 16.6 "US" 21700 2 . 16.4 16.75 "US" 21703 3 . 16.5 16.5 "US" 21704 1 . 15.5 16.65 "US" 21705 1 . 16.95 16.95 "US" 21707 2 . 15.8 16.85 "US" 21710 3 . 16.15 16.9 "US" 21711 1 . 16.9 16.9 "US" 21712 1 . 16 16.3 "US" 21713 1 . 15.95 15.95 "US" 21714 1 . 15.95 16 "US" 21717 3 . 15.85 15.9 "US" 21718 1 . 15.6 15.8 "US" 21719 1 . 16 16 "US" 21720 1 . 15.75 15.75 "US" 21724 4 1 15.45 15.55 "US" 21725 1 . 15.55 16.1 "US" 21726 1 . 15.6 15.6 "US" 21727 1 . 15 15.5 "US" 21728 1 . 15.45 15.8 "US" 21731 3 . 15.75 16.75 "US" 21732 1 . 16.8 16.8 "US" 21733 1 . 16.5 16.5 "US" 21734 1 . 16.2 16.2 "US" 21735 1 . 15.7 15.75 "US" 21738 3 . 15.65 15.65 "US" 21739 1 . 15.25 15.45 "US" 21740 1 . 14.95 15.4 "US" 21741 1 . 15.15 15.5 "US" 21742 1 . 15.05 16.25 "US" 21745 3 . 15.25 15.25 "US" 21746 1 . 15.2 15.35 "US" 21747 1 . 15.25 17.3 "US" 21748 1 . 17 17.25 "US" 21749 1 . 16.25 16.25 "US" 21752 3 . 15.7 16.05 "US" 21753 1 . 16.3 16.95 "US" 21754 1 . 16.15 16.5 "US" 21755 1 . 16.1 16.35 "US" 21756 1 . 16.2 16.3 "US" 21759 3 . 16 16.25 "US" 21760 1 . 15.6 15.95 "US" 21761 1 . 15.75 16.15 "US" 21762 1 . 16.05 16.05 "US" 21763 1 . 15.5 15.75 end format %td date
So the expanded sequences (added at the bottom of my dataset) would be:
Code:
"Sweden1" 21614 . 1 8.72 8.74 "Sweden1" 21615 1 . 8.32 8.72 "Sweden1" 21616 1 . 8.44 8.68 "Sweden1" 21619 3 . 8.66 8.9 "Sweden1" 21620 1 . 8.8 8.8 "Sweden1" 21621 1 . 8.88 8.9 "Sweden1" 21622 1 . 8.78 8.8 "Sweden1" 21623 1 . 8.88 8.98 "Sweden1" 21626 3 . 8.98 8.98 "Sweden1" 21627 1 . 8.78 8.9 end format %td date
Code:
The second: "Sweden2" 21615 1 . 8.32 8.72 "Sweden2" 21616 1 . 8.44 8.68 "Sweden2" 21619 3 . 8.66 8.9 "Sweden2" 21620 1 . 8.8 8.8 "Sweden2" 21621 1 . 8.88 8.9 "Sweden2" 21622 1 . 8.78 8.8 "Sweden2" 21623 1 . 8.88 8.98 "Sweden2" 21626 3 . 8.98 8.98 "Sweden2" 21627 1 . 8.78 8.9 "Sweden2" 21628 1 . 8.9 8.9 end format %td date
Code:
The third: "Sweden3" 21616 1 . 8.44 8.68 "Sweden3" 21619 3 . 8.66 8.9 "Sweden3" 21620 1 . 8.8 8.8 "Sweden3" 21621 1 . 8.88 8.9 "Sweden3" 21622 1 . 8.78 8.8 "Sweden3" 21623 1 . 8.88 8.98 "Sweden3" 21626 3 . 8.98 8.98 "Sweden3" 21627 1 . 8.78 8.9 "Sweden3" 21628 1 . 8.9 8.9 "Sweden3" 21629 1 . 8.76 8.9 end format %td date
To summarize. Add rolling sequencies of ten consecutive observations with maximum 3 days between observations, within ID.
Dummy code:
local k =1
some kind of loop {
bys ID: expand 10 if longgap!=1, start at observation==`k'
local k `++k'
}
