Append 5 longitudinal dataset

[Ali Abutaleb]

I have a five-datasets (longitudinal dataset) from (the labour force survey LFS in the UK) from April 2019 to June 2021. LFS has a rotating panel, so we can follow people for five conceive quarters, so each longitudinal dataset has five quarters. for example for the first dataset, April 2019 to June 2020, shown as :

* Example generated by -dataex-. For more info, type help dataex
clear
input byte quarter
1
2
3
4
5
end
label values quarter quarter
label def quarter 1 "Apr-Jun 2019", modify
label def quarter 2 "Jul-Sept 2019", modify
label def quarter 3 "Oct-Dec 2019", modify
label def quarter 4 "Jan-Mar 2020", modify
label def quarter 5 "Apr-Jun 2020", modify

The second dataset :

* Example generated by -dataex-. For more info, type help dataex
clear
input byte quarter
1
2
3
4
5
end
label values quarter quarter
label def quarter 1 "July-Seb 2019", modify
label def quarter 2 "Oct-Des 2019", modify
label def quarter 3 "Jan-Mar 2020", modify
label def quarter 4 "Apr-Jun 2020", modify
label def quarter 5 "July-Seb 2020", modify

Third dataset :

* Example generated by -dataex-. For more info, type help dataex
clear
input byte quarter
1
2
3
4
5
end
label values quarter quarter
label def quarter 1 "Oct-Des 2019", modify
label def quarter 2 "Jan-Mar 2020", modify
label def quarter 3 "Apr-June 2020", modify
label def quarter 4 "July-Sep 2020", modify
label def quarter 5 "Oct-Des 2020", modify

Forth :

* Example generated by -dataex-. For more info, type help dataex
clear
input byte quarter
1
2
3
4
5
end
label values quarter quarter
label def quarter 1 "Jan-Mar 2020", modify
label def quarter 2 "Apr-June 2020", modify
label def quarter 3 "July-Sep 2020", modify
label def quarter 4 "Oct-Des 2020", modify
label def quarter 5 "Jan-Mar 2021", modify

Last one:

* Example generated by -dataex-. For more info, type help dataex
clear
input byte quarter
1
2
3
4
5
end
label values quarter quarter
label def quarter 1 "Apr-June 2020", modify
label def quarter 2 "July-Sep 2020", modify
label def quarter 3 "Oct-Des 2020", modify
label def quarter 4 "Jan-Mar 2021", modify
label def quarter 5 "Apr-June 2021", modify

My question is :

How can I append all five datasets in one long dataset with define by the time index as shown on the original datasets?

I tried to append with this command :

sysuse April2019_June2020_ready, replace
append using July2019_Seb2020_ready Oct2019_Des2020_ready  Jan2020_Mar2021_ready_ready April2020_June2021_ready
sort PERSID quarter 
save ZHC_April2019_June2021, replace

But I found the data has just been defined by the time index of the first dataset (April 2019 to June 2020), not including all the time of the five datasets! as this :

* Example generated by -dataex-. For more info, type help dataex
clear
input byte quarter
1
2
3
4
5
1
2
3
4
5
1
2
3
4
5
1
2
3
4
5
1
2
3
4
5
1
2
3
4
5
1
2
3
4
5
1
2
3
4
5
1
2
3
4
5
1
2
3
4
5
1
2
3
4
5
1
2
3
4
5
1
2
3
4
5
1
2
3
4
5
1
2
3
4
5
1
2
3
4
5
1
2
3
4
5
1
2
3
4
5
1
2
3
4
5
1
2
3
4
5
end
label values quarter quarter
label def quarter 1 "Apr-Jun 2019", modify
label def quarter 2 "Jul-Sept 2019", modify
label def quarter 3 "Oct-Dec 2019", modify
label def quarter 4 "Jan-Mar 2020", modify
label def quarter 5 "Apr-Jun 2020", modify

How can I solve this to show all the time ?


Thanks in advance

[William Lisowski]

Here is a brief example using just the first two datasets

use "`data1'", clear
generate ds = 1
list
tempfile d1
save "`d1'"

use "`data2'", clear
generate ds = 2
list
tempfile d2
save "`d2'"

clear
append using "`d1'" "`d2'"
label drop quarter
replace quarter = quarter+ds-1
list
label def QUARTER 1 "Apr-Jun 2019"  ///
                  2 "Jul-Sept 2019" ///
                  3 "Oct-Dec 2019"  ///
                  4 "Jan-Mar 2020"  ///
                  5 "Apr-Jun 2020"  ///
                  6 "Jul-Sept 2020" 
label values quarter QUARTER
list

. use "`data1'", clear

. generate ds = 1

. list

     +--------------------+
     |       quarter   ds |
     |--------------------|
  1. |  Apr-Jun 2019    1 |
  2. | Jul-Sept 2019    1 |
  3. |  Oct-Dec 2019    1 |
  4. |  Jan-Mar 2020    1 |
  5. |  Apr-Jun 2020    1 |
     +--------------------+

. tempfile d1

. save "`d1'"
file /var/folders/xr/lm5ccr996k7dspxs35yqzyt80000gp/T//S_04011.000003 saved as .dta format

. 
. use "`data2'", clear

. generate ds = 2

. list

     +--------------------+
     |       quarter   ds |
     |--------------------|
  1. | July-Seb 2019    2 |
  2. |  Oct-Des 2019    2 |
  3. |  Jan-Mar 2020    2 |
  4. |  Apr-Jun 2020    2 |
  5. | July-Seb 2020    2 |
     +--------------------+

. tempfile d2

. save "`d2'"
file /var/folders/xr/lm5ccr996k7dspxs35yqzyt80000gp/T//S_04011.000004 saved as .dta format

. 
. clear

. append using "`d1'" "`d2'"
(label quarter already defined)

. label drop quarter

. replace quarter = quarter+ds-1
(5 real changes made)

. list

     +--------------+
     | quarter   ds |
     |--------------|
  1. |       1    1 |
  2. |       2    1 |
  3. |       3    1 |
  4. |       4    1 |
  5. |       5    1 |
     |--------------|
  6. |       2    2 |
  7. |       3    2 |
  8. |       4    2 |
  9. |       5    2 |
 10. |       6    2 |
     +--------------+

. label def QUARTER 1 "Apr-Jun 2019"  ///
>                   2 "Jul-Sept 2019" ///
>                   3 "Oct-Dec 2019"  ///
>                   4 "Jan-Mar 2020"  ///
>                   5 "Apr-Jun 2020"  ///
>                   6 "Jul-Sept 2020" 

. label values quarter QUARTER

. list

     +--------------------+
     |       quarter   ds |
     |--------------------|
  1. |  Apr-Jun 2019    1 |
  2. | Jul-Sept 2019    1 |
  3. |  Oct-Dec 2019    1 |
  4. |  Jan-Mar 2020    1 |
  5. |  Apr-Jun 2020    1 |
     |--------------------|
  6. | Jul-Sept 2019    2 |
  7. |  Oct-Dec 2019    2 |
  8. |  Jan-Mar 2020    2 |
  9. |  Apr-Jun 2020    2 |
 10. | Jul-Sept 2020    2 |
     +--------------------+

.

[William Lisowski]

A better approach might be the following.

clear
append using "`d1'" "`d2'"
label drop quarter
label values quarter
replace quarter = quarter+ds-1
list
generate qtr = yq(2019,1)+quarter
format %tq qtr
list

. clear

. append using "`d1'" "`d2'"
(label quarter already defined)

. label drop quarter

. label values quarter

. replace quarter = quarter+ds-1
(5 real changes made)

. list

     +--------------+
     | quarter   ds |
     |--------------|
  1. |       1    1 |
  2. |       2    1 |
  3. |       3    1 |
  4. |       4    1 |
  5. |       5    1 |
     |--------------|
  6. |       2    2 |
  7. |       3    2 |
  8. |       4    2 |
  9. |       5    2 |
 10. |       6    2 |
     +--------------+

. generate qtr = yq(2019,1)+quarter

. format %tq qtr

. list

     +-----------------------+
     | quarter   ds      qtr |
     |-----------------------|
  1. |       1    1   2019q2 |
  2. |       2    1   2019q3 |
  3. |       3    1   2019q4 |
  4. |       4    1   2020q1 |
  5. |       5    1   2020q2 |
     |-----------------------|
  6. |       2    2   2019q3 |
  7. |       3    2   2019q4 |
  8. |       4    2   2020q1 |
  9. |       5    2   2020q2 |
 10. |       6    2   2020q3 |
     +-----------------------+

.