Adding new observations and replace

[Tharshini Thangavelu]

Dear all,


I would like to add 12 rows in variable år after 2020 for all lopnr_personid and then replace it with value of årKontroll_NRV tmp_år_tjp.
So basically create 2021 and replace the value of 2020 of variables årKontroll_NRV tmp_år_tjp for 2021.

* Example generated by -dataex-. For more info, type help dataex
clear
input double lopnr_personid float(år årKontroll_NRV tmp_år_tjp)
1 2020 0 0
1 2020 0 0
1 2020 0 0
1 2020 0 0
1 2020 0 0
1 2020 0 0
1 2020 0 0
1 2020 0 0
1 2020 0 0
1 2020 0 0
1 2020 0 0
1 2020 0 0
end

Thanks !

[Carlo Lazzaro]

Tharshini:
you may want to conside

. input double lopnr_personid float(år årKontroll_NRV tmp_år_tjp)

     lopnr_pe~d        år  årKontr~V  tmp_år_~p
  1. 1 2020 0 0
  2. 1 2020 0 0
  3. 1 2020 0 0
  4. 1 2020 0 0
  5. 1 2020 0 0
  6. 1 2020 0 0
  7. 1 2020 0 0
  8. 1 2020 0 0
  9. 1 2020 0 0
 10. 1 2020 0 0
 11. 1 2020 0 0
 12. 1 2020 0 0
 13. end

. expand 2
(12 observations created)

. replace år=2021 in 13/24
(12 real changes made)

. replace årKontroll_NRV=runiform() if år==2021
(12 real changes made)

. list

     +---------------------------------------+
     | lopnr_~d     år   årKont~V   tmp_år~p |
     |---------------------------------------|
  1. |        1   2020          0          0 |
  2. |        1   2020          0          0 |
  3. |        1   2020          0          0 |
  4. |        1   2020          0          0 |
  5. |        1   2020          0          0 |
     |---------------------------------------|
  6. |        1   2020          0          0 |
  7. |        1   2020          0          0 |
  8. |        1   2020          0          0 |
  9. |        1   2020          0          0 |
 10. |        1   2020          0          0 |
     |---------------------------------------|
 11. |        1   2020          0          0 |
 12. |        1   2020          0          0 |
 13. |        1   2021   .3488717          0 |
 14. |        1   2021   .2668857          0 |
 15. |        1   2021   .1366463          0 |
     |---------------------------------------|
 16. |        1   2021   .0285569          0 |
 17. |        1   2021   .8689333          0 |
 18. |        1   2021   .3508549          0 |
 19. |        1   2021   .0711051          0 |
 20. |        1   2021    .323368          0 |
     |---------------------------------------|
 21. |        1 2021   .5551032          0 |
 22. |        1   2021    .875991          0 |
 23. |        1   2021   .2047095          0 |
 24. |        1   2021   .8927587          0 |
     +---------------------------------------+

.

PS: -årKontroll_NRV- for 2021 is the result of a trivial assumption.

[Tharshini Thangavelu]

Thanks! I need to make a clarification, I realized that the variable år is not contant across the identifier lopnr_personid this means that the number of rows differs within the identifier lopnr_personid.
So what I want is to copy and paste values of variable år==2020 and create year 2021 for all variables in the dataset.

The commands that were given does not do this completely.

[Carlo Lazzaro]

Tharshini:
could you please provide an excerpt of this updated scenario? Thanks.

[Tharshini Thangavelu]

Here comes an excerpt for the entire lopnr_personid ==1. So copy år==2020 and add 2021 for variable år and paste below for all identifier lopnr_personid.
Hope this makes sense. I have, of course only included some of the many variables that the data material includes.

* Example generated by -dataex-. For more info, type help dataex
clear
input double lopnr_personid float(startdatum avslutsdatum) double år_månad float(månad_nr år år_nr år_start år_avslut)
1 20921 21000 648 -39 2014 -4 2017 2017
1 20921 21000 649 -38 2014 -4 2017 2017
1 20921 21000 650 -37 2014 -4 2017 2017
1 20921 21000 651 -36 2014 -4 2017 2017
1 20921 21000 652 -35 2014 -4 2017 2017
1 20921 21000 653 -34 2014 -4 2017 2017
1 20921 21000 654 -33 2014 -4 2017 2017
1 20921 21000 655 -32 2014 -4 2017 2017
1 20921 21000 656 -31 2014 -4 2017 2017
1 20921 21000 657 -30 2014 -4 2017 2017
1 20921 21000 658 -29 2014 -4 2017 2017
1 20921 21000 659 -28 2014 -4 2017 2017
1 20921 21000 660 -27 2015 -3 2017 2017
1 20921 21000 661 -26 2015 -3 2017 2017
1 20921 21000 662 -25 2015 -3 2017 2017
1 20921 21000 663 -24 2015 -3 2017 2017
1 20921 21000 664 -23 2015 -3 2017 2017
1 20921 21000 665 -22 2015 -3 2017 2017
1 20921 21000 666 -21 2015 -3 2017 2017
1 20921 21000 667 -20 2015 -3 2017 2017
1 20921 21000 668 -19 2015 -3 2017 2017
1 20921 21000 669 -18 2015 -3 2017 2017
1 20921 21000 670 -17 2015 -3 2017 2017
1 20921 21000 671 -16 2015 -3 2017 2017
1 20921 21000 672 -15 2016 -2 2017 2017
1 20921 21000 673 -14 2016 -2 2017 2017
1 20921 21000 674 -13 2016 -2 2017 2017
1 20921 21000 675 -12 2016 -2 2017 2017
1 20921 21000 676 -11 2016 -2 2017 2017
1 20921 21000 677 -10 2016 -2 2017 2017
1 20921 21000 678  -9 2016 -2 2017 2017
1 20921 21000 679  -8 2016 -2 2017 2017
1 20921 21000 680  -7 2016 -2 2017 2017
1 20921 21000 681  -6 2016 -2 2017 2017
1 20921 21000 682  -5 2016 -2 2017 2017
1 20921 21000 683  -4 2016 -2 2017 2017
1 20921 21000 684  -3 2017 -1 2017 2017
1 20921 21000 685  -2 2017 -1 2017 2017
1 20921 21000 686  -1 2017 -1 2017 2017
1 20921 21000 689   0 2017  0 2017 2017
1 20921 21000 690   1 2017  0 2017 2017
1 20921 21000 691   2 2017  0 2017 2017
1 20921 21000 692   3 2017  0 2017 2017
1 20921 21000 693   4 2017  0 2017 2017
1 20921 21000 694   5 2017  0 2017 2017
1 20921 21000 695   6 2017  0 2017 2017
1 20921 21000 696   7 2018  1 2017 2017
1 20921 21000 697   8 2018  1 2017 2017
1 20921 21000 698   9 2018  1 2017 2017
1 20921 21000 699  10 2018  1 2017 2017
1 20921 21000 700  11 2018  1 2017 2017
1 20921 21000 701  12 2018  1 2017 2017
1 20921 21000 702  13 2018  1 2017 2017
1 20921 21000 703  14 2018  1 2017 2017
1 20921 21000 704  15 2018  1 2017 2017
1 20921 21000 705  16 2018  1 2017 2017
1 20921 21000 706  17 2018  1 2017 2017
1 20921 21000 707  18 2018  1 2017 2017
1 20921 21000 708  19 2019  2 2017 2017
1 20921 21000 709  20 2019  2 2017 2017
1 20921 21000 710  21 2019  2 2017 2017
1 20921 21000 711  22 2019  2 2017 2017
1 20921 21000 712  23 2019  2 2017 2017
1 20921 21000 713  24 2019  2 2017 2017
1 20921 21000 714  25 2019  2 2017 2017
1 20921 21000 715  26 2019  2 2017 2017
1 20921 21000 716  27 2019  2 2017 2017
1 20921 21000 717  28 2019  2 2017 2017
1 20921 21000 718  29 2019  2 2017 2017
1 20921 21000 719  30 2019  2 2017 2017
1 20921 21000 720  31 2020  3 2017 2017
1 20921 21000 721  32 2020  3 2017 2017
1 20921 21000 722  33 2020  3 2017 2017
1 20921 21000 723  34 2020  3 2017 2017
1 20921 21000 724  35 2020  3 2017 2017
1 20921 21000 725  36 2020  3 2017 2017
1 20921 21000 726  37 2020  3 2017 2017
1 20921 21000 727  38 2020  3 2017 2017
1 20921 21000 728  39 2020  3 2017 2017
1 20921 21000 729  40 2020  3 2017 2017
1 20921 21000 730  41 2020  3 2017 2017
end
format %tdCCYYNNDD startdatum
format %tdCCYYNNDD avslutsdatum
format %tmCCYYNN år_månad

[Tharshini Thangavelu]

Here comes another try, the first data shows the result from the the code provided in reply #2. However the second data shows that 2021 does not copy the 2020's values. I am uncertain of why this does this way.
Also, if I would like to have 24 rows instead of 12 rows, that is the 2020's values is copied twice so that I will have 2 rows for each month ( as shown in #5). How do I go about to do this?

* Example generated by -dataex-. For more info, type help dataex
clear
input double lopnr_personid float(startdatum avslutsdatum) double år_månad float(månad_nr år år_nr år_start år_avslut)
1 20921 21000 660 -27 2021 -3 2017 2017
1 20921 21000 661 -26 2021 -3 2017 2017
1 20921 21000 662 -25 2021 -3 2017 2017
1 20921 21000 663 -24 2021 -3 2017 2017
1 20921 21000 664 -23 2021 -3 2017 2017
1 20921 21000 665 -22 2021 -3 2017 2017
1 20921 21000 666 -21 2021 -3 2017 2017
1 20921 21000 667 -20 2021 -3 2017 2017
1 20921 21000 668 -19 2021 -3 2017 2017
1 20921 21000 669 -18 2021 -3 2017 2017
1 20921 21000 670 -17 2021 -3 2017 2017
1 20921 21000 671 -16 2021 -3 2017 2017
end
format %tdCCYYNNDD startdatum
format %tdCCYYNNDD avslutsdatum
format %tmCCYYNN år_månad

The dataset here below shows the values for 2020

* Example generated by -dataex-. For more info, type help dataex
clear
input long lopnr_personid float(startdatum avslutsdatum) double år_månad float(månad_nr år år_start år_avslut år_nr)
1 20921 21000 720 31 2020 2017 2017 3
1 20921 21000 721 32 2020 2017 2017 3
1 20921 21000 722 33 2020 2017 2017 3
1 20921 21000 723 34 2020 2017 2017 3
1 20921 21000 724 35 2020 2017 2017 3
1 20921 21000 725 36 2020 2017 2017 3
1 20921 21000 726 37 2020 2017 2017 3
1 20921 21000 727 38 2020 2017 2017 3
1 20921 21000 728 39 2020 2017 2017 3
1 20921 21000 729 40 2020 2017 2017 3
1 20921 21000 730 41 2020 2017 2017 3
1 20921 21000 731 42 2020 2017 2017 3
end
format %tdCCYYNNDD startdatum
format %tdCCYYNNDD avslutsdatum
format %tmCCYYNN år_månad

[Carlo Lazzaro]

Tharshini:
I fail to get what you're after.
That said, if you want 24 instead of 12 observations, just type:

expand 3

[Tharshini Thangavelu]

Sorry, its my fault, I seems to have misunderstood my data. There should be 12 obs per year. However, when using the command

expand 2

, it creates 24 observations which made me confuse the dataset hence my lack of understanding the data.

So let me try again to explain, I want to add 2021 for variable år and copying the values of 2020. But currently, the code doesn't do this instead it creates 12 rows with 2021 ( which is correct) but for år_månad it should be 202001 202002 ect (see the previous replies with the dataset)

Does this make sense to you?

[Carlo Lazzaro]

Tharshini:
surely, there are more efficient code, but yu may want to try:

. generate month = month( år_månad )

. generate year = year( år_månad )

. replace month=_n

. replace year=2020

. expand 2

. replace year=2021 in 13/24

. replace år_månad = ym(year,month) in 13/24

. replace år=2021 in 13/24

. drop month year

. list

     +-------------------------------------------------------------------------------------------+
     | lopnr_~d   startd~m   avslut~m   år_månad   månad_nr     år   år_start   år_avs~t   år_nr |
     |-------------------------------------------------------------------------------------------|
  1. |        1   20170412   20170630     202001         31   2020       2017       2017       3 |
  2. |        1   20170412   20170630     202002         32   2020       2017       2017       3 |
  3. |        1   20170412   20170630     202003         33   2020       2017       2017       3 |
  4. |        1   20170412   20170630     202004         34   2020       2017       2017       3 |
  5. |        1   20170412   20170630     202005         35   2020       2017       2017       3 |
     |-------------------------------------------------------------------------------------------|
  6. |        1   20170412   20170630     202006         36   2020       2017       2017       3 |
  7. |        1   20170412   20170630     202007         37   2020       2017       2017       3 |
  8. |        1   20170412   20170630     202008         38   2020       2017       2017       3 |
  9. |        1   20170412   20170630     202009         39   2020       2017       2017       3 |
 10. |        1   20170412   20170630     202010         40   2020       2017       2017       3 |
     |-------------------------------------------------------------------------------------------|
 11. |        1   20170412   20170630     202011         41   2020       2017       2017       3 |
 12. |        1   20170412   20170630     202012         42   2020       2017       2017       3 |
 13. |        1   20170412   20170630     202101         31   2021       2017       2017       3 |
 14. |        1   20170412   20170630     202102         32   2021       2017       2017       3 |
 15. |        1   20170412   20170630     202103         33   2021       2017       2017       3 |
     |-------------------------------------------------------------------------------------------|
 16. |        1   20170412   20170630     202104         34   2021       2017       2017       3 |
 17. |        1   20170412   20170630     202105         35   2021       2017       2017       3 |
 18. |        1   20170412   20170630     202106         36   2021       2017       2017       3 |
 19. |        1   20170412   20170630     202107         37   2021       2017       2017       3 |
 20. |        1   20170412   20170630     202108         38   2021       2017       2017       3 |
     |-------------------------------------------------------------------------------------------|
 21. |        1   20170412   20170630     202109         39   2021       2017       2017       3 |
 22. |        1   20170412   20170630     202110         40   2021       2017       2017       3 |
 23. |        1   20170412   20170630     202111         41   2021       2017       2017       3 |
 24. |        1   20170412   20170630     202112         42   2021       2017       2017       3 |
     +-------------------------------------------------------------------------------------------+

.

[Tharshini Thangavelu]

Thanks, but it doesn't seems to be working for me though. I get the following results. I still get copies which means that I have 24 obs for all other years expect for year 2021 which should not be the case and the code

 
 replace år_månad = ym(year,month) in 13/24

gives only missing as you can see in the excerpt for 2020 and 2021. Other variables doesn't copy from 2020 either such as år_nr and månad_nr. Somewhat strange?

* Example generated by -dataex-. For more info, type help dataex
clear
input double lopnr_personid float(startdatum avslutsdatum) double år_månad float(månad_nr år år_nr år_start år_avslut)
1 20921 21000 720  31 2020  3 2017 2017
1 20921 21000 720  31 2020  3 2017 2017
1 20921 21000 721  32 2020  3 2017 2017
1 20921 21000 721  32 2020  3 2017 2017
1 20921 21000 722  33 2020  3 2017 2017
1 20921 21000 722  33 2020  3 2017 2017
1 20921 21000 723  34 2020  3 2017 2017
1 20921 21000 723  34 2020  3 2017 2017
1 20921 21000 724  35 2020  3 2017 2017
1 20921 21000 724  35 2020  3 2017 2017
1 20921 21000 725  36 2020  3 2017 2017
1 20921 21000 725  36 2020  3 2017 2017
1 20921 21000 726  37 2020  3 2017 2017
1 20921 21000 726  37 2020  3 2017 2017
1 20921 21000 727  38 2020  3 2017 2017
1 20921 21000 727  38 2020  3 2017 2017
1 20921 21000 728  39 2020  3 2017 2017
1 20921 21000 728  39 2020  3 2017 2017
1 20921 21000 729  40 2020  3 2017 2017
1 20921 21000 729  40 2020  3 2017 2017
1 20921 21000 730  41 2020  3 2017 2017
1 20921 21000 730  41 2020  3 2017 2017
1 20921 21000 731  42 2020  3 2017 2017
1 20921 21000 731  42 2020  3 2017 2017
1 20921 21000   . -27 2021 -3 2017 2017
1 20921 21000   . -26 2021 -3 2017 2017
1 20921 21000   . -25 2021 -3 2017 2017
1 20921 21000   . -24 2021 -3 2017 2017
1 20921 21000   . -23 2021 -3 2017 2017
1 20921 21000   . -22 2021 -3 2017 2017
1 20921 21000   . -21 2021 -3 2017 2017
1 20921 21000   . -20 2021 -3 2017 2017
1 20921 21000   . -19 2021 -3 2017 2017
1 20921 21000   . -18 2021 -3 2017 2017
1 20921 21000   . -17 2021 -3 2017 2017
1 20921 21000   . -16 2021 -3 2017 2017
end
format %tdCCYYNNDD startdatum
format %tdCCYYNNDD avslutsdatum
format %tmCCYYNN år_månad

[Carlo Lazzaro]

Tharshini:
sorry, but I do not think that I could be more helpful.

[Ken Chui]

This may be simpler.

Keep only 2020:

keep if year == 2020

And through this step you can control which 12 cases of 2020 are to be copied.

Replace 2020 into 2021:

replace year = 2021 if year == 2020

Save it as a new data set:

save y2021, replace

Now, go back to the original data, and append 2021 to it:

use OriginalData, clear
append using y2021