Create a daily dataset with rules

[Marco Errico]

Hello everyone
I’m trying to define some rules on a dataset that comes from a regulator. I provided an example below.
Let me apologies firstly if I create any sort of confusion with an issue which I've been facing recently with this dataset. Unfortunately the data are a bit messy on how they are aggregated and I'm trying to clean them in the best way.


My main goal is to create a new panel with:

• Id (isin in this case)
• a new variable that identifies the time dimension
• and a new variable named adnt_applicable

Finally I will store the new dataset separately.

1. The variable time dimension should be daily and has not to have any gap from January 2018 till now.

2. For each day and id of the new time dimension, the new adnt applicable has to be the value contained in the variable adnt_old with the following rules:

When reporting_date_start is 1^st January 2017 and reporting_date_end is 31th December 2017, then the value contained in the variable adnt_old will be applied for the new variable adnt_applicable from 1^st January 2018 to 31th March 2019 (of the new time dimension variable). And also, there might be different adnt_old with same reporting period and isin. My intention is always to pick the value of adnt_old when the variable daily_filedate is missing

When reporting_date_start is 1^st January 2018 and reporting_date_end is 31th December 2018, then the value contained in the variable adnt_old will be applied for the new adnt_applicable from 1^st April 2019 to 31th March 2020 (of the new time dimension variable). Here, I want to track when adnt_old is changing but just when it happened in a daily_filedate between (1^st April 2019 to 31th March 2020). With of course same reporting_date_ start and end (2018)
Otherwise I ignore the change (if for example the daily_ filedate is in year 2021),

When reporting_date_start is 1^st January 2019 and reporting_date_end is 31th December 2019, then the value contained in the variable adnt_old will be applied for the new adnt_applicable from 1^st April 2020 to 31th March 2021. Same as point before, I want to track when adnt_old is changing but just when it happened in a daily_file date between (1^st April 2020 to 31th March 2021) with of course same reporting_date_ start and end (2019)

I acknowledge that I might have created confusion in explaining as well as the final goal might be a bit challenging but it would be wonderful if someone has any tip for that.

Thanks a lot in advance for the time and kindness.

* Example generated by -dataex-. To install: ssc install dataex
clear
input str12 isin float(daily_filedate reporting_date_start reporting_date_end adnt_old)
"AEDFXA1EN018"     . 20820 21184     30.73
"AEDFXA1EN018" 21519 20820 21184 30.733946
"AEDFXA1EN018" 21813 20820 21184 30.733946
"AEDFXA1EN018" 21575 20820 21184 30.733946
"AEDFXA1EN018" 21687 20820 21184 30.733946
"AEDFXA1EN018" 21736 20820 21184 30.733946
"AEDFXA1EN018" 21876 20820 21184 30.733946
"AEDFXA1EN018" 21666 20820 21184 30.733946
"AEDFXA1EN018" 21883 20820 21184 30.733946
"AEDFXA1EN018" 21428 20820 21184 30.733946
"AEDFXA1EN018" 21862 20820 21184 30.733946
"AEDFXA1EN018" 21463 20820 21184 30.733946
"AEDFXA1EN018" 21701 20820 21184 30.733946
"AEDFXA1EN018" 21414 20820 21184 30.733946
"AEDFXA1EN018" 21680 20820 21184 30.733946
"AEDFXA1EN018" 21421 20820 21184 30.733946
"AEDFXA1EN018" 21603 20820 21184 30.733946
"AEDFXA1EN018" 21505 20820 21184 30.733946
"AEDFXA1EN018" 21848 20820 21184 30.733946
"AEDFXA1EN018" 21841 20820 21184 30.733946
"AEDFXA1EN018" 21435 20820 21184 30.733946
"AEDFXA1EN018" 21568 20820 21184 30.733946
"AEDFXA1EN018" 21582 20820 21184 30.733946
"AEDFXA1EN018" 21771 20820 21184 30.733946
"AEDFXA1EN018" 21764 20820 21184 30.733946
"AEDFXA1EN018" 21652 20820 21184 30.733946
"AEDFXA1EN018" 21778 20820 21184 30.733946
"AEDFXA1EN018" 21820 20820 21184 30.733946
"AEDFXA1EN018" 21659 20820 21184 30.733946
"AEDFXA1EN018" 21456 20820 21184 30.733946
"AEDFXA1EN018" 21827 20820 21184 30.733946
"AEDFXA1EN018" 21631 20820 21184 30.733946
"AEDFXA1EN018" 21596 20820 21184 30.733946
"AEDFXA1EN018" 21792 20820 21184 30.733946
"AEDFXA1EN018" 21743 20820 21184 30.733946
"AEDFXA1EN018" 21442 20820 21184 30.733946
"AEDFXA1EN018" 21449 20820 21184 30.733946
"AEDFXA1EN018" 21638 20820 21184 30.733946
"AEDFXA1EN018" 21715 20820 21184 30.733946
"AEDFXA1EN018" 21512 20820 21184 30.733946
"AEDFXA1EN018" 21477 20820 21184 30.733946
"AEDFXA1EN018" 21799 20820 21184 30.733946
"AEDFXA1EN018" 21869 20820 21184 30.733946
"AEDFXA1EN018" 21722 20820 21184 30.733946
"AEDFXA1EN018" 21526 20820 21184 30.733946
"AEDFXA1EN018" 21393 20820 21184 30.733946
"AEDFXA1EN018" 21610 20820 21184 30.733946
"AEDFXA1EN018" 21484 20820 21184 30.733946
"AEDFXA1EN018" 21834 20820 21184 30.733946
"AEDFXA1EN018" 21708 20820 21184 30.733946
"AEDFXA1EN018" 21645 20820 21184 30.733946
"AEDFXA1EN018" 21498 20820 21184 30.733946
"AEDFXA1EN018" 21806 20820 21184 30.733946
"AEDFXA1EN018" 21750 20820 21184 30.733946
"AEDFXA1EN018" 21561 20820 21184 30.733946
"AEDFXA1EN018" 21554 20820 21184 30.733946
"AEDFXA1EN018" 21491 20820 21184 30.733946
"AEDFXA1EN018" 21540 20820 21184 30.733946
"AEDFXA1EN018" 21400 20820 21184 30.733946
"AEDFXA1EN018" 21785 20820 21184 30.733946
"AEDFXA1EN018" 21729 20820 21184 30.733946
"AEDFXA1EN018" 21589 20820 21184 30.733946
"AEDFXA1EN018" 21547 20820 21184 30.733946
"AEDFXA1EN018" 21757 20820 21184 30.733946
"AEDFXA1EN018" 21855 20820 21184 30.733946
"AEDFXA1EN018" 21407 20820 21184 30.733946
"AEDFXA1EN018" 21673 20820 21184 30.733946
"AEDFXA1EN018" 22485 21550 21914    .00391
"AEDFXA1EN018" 22296 21550 21914    .00391
"AEDFXA1EN018" 22527 21550 21914    .00391
"AEDFXA1EN018" 22548 21550 21914    .00391
"AEDFXA1EN018" 22415 21550 21914    .00391
"AEDFXA1EN018" 22373 21550 21914    .00391
"AEDFXA1EN018" 22492 21550 21914    .00391
"AEDFXA1EN018" 22541 21550 21914    .00391
"AEDFXA1EN018" 22520 21550 21914    .00391
"AEDFXA1EN018" 22359 21550 21914    .00391
"AEDFXA1EN018" 22436 21550 21914    .00391
"AEDFXA1EN018" 22443 21550 21914    .00391
"AEDFXA1EN018" 22394 21550 21914    .00391
"AEDFXA1EN018" 22303 21550 21914    .00391
"AEDFXA1EN018" 22506 21550 21914    .00391
"AEDFXA1EN018" 22352 21550 21914    .00391
"AEDFXA1EN018" 22513 21550 21914    .00391
"AEDFXA1EN018" 22324 21550 21914    .00391
"AEDFXA1EN018" 22331 21550 21914    .00391
"AEDFXA1EN018" 22464 21550 21914    .00391
"AEDFXA1EN018" 22380 21550 21914    .00391
"AEDFXA1EN018" 22555 21550 21914    .00391
"AEDFXA1EN018" 22408 21550 21914    .00391
"AEDFXA1EN018" 22345 21550 21914    .00391
"AEDFXA1EN018" 22366 21550 21914    .00391
"AEDFXA1EN018" 22450 21550 21914    .00391
"AEDFXA1EN018" 22499 21550 21914    .00391
"AEDFXA1EN018" 22478 21550 21914    .00391
"AEDFXA1EN018" 22387 21550 21914    .00391
"AEDFXA1EN018" 22422 21550 21914    .00391
"AEDFXA1EN018" 22338 21550 21914    .00391
"AEDFXA1EN018" 22401 21550 21914    .00391
"AEDFXA1EN018" 22429 21550 21914    .00391
end
format %d daily_filedate
format %td reporting_date_start
format %td reporting_date_end

[Andrew Musau]

I do not completely follow your rules on the new variable. However, you can define a new fiscal year variable and use time series operators to create the variable, but this should get you started on your goal. Assuming that your predefined values are correct:

* Example generated by -dataex-. To install: ssc install dataex
clear
input str12 isin float(daily_filedate reporting_date_start reporting_date_end adnt_old)
"AEDFXA1EN018"     . 20820 21184     30.73
"AEDFXA1EN018" 21519 20820 21184 30.733946
"AEDFXA1EN018" 21813 20820 21184 30.733946
"AEDFXA1EN018" 21575 20820 21184 30.733946
"AEDFXA1EN018" 21687 20820 21184 30.733946
"AEDFXA1EN018" 21736 20820 21184 30.733946
"AEDFXA1EN018" 21876 20820 21184 30.733946
"AEDFXA1EN018" 21666 20820 21184 30.733946
"AEDFXA1EN018" 21883 20820 21184 30.733946
"AEDFXA1EN018" 21428 20820 21184 30.733946
"AEDFXA1EN018" 21862 20820 21184 30.733946
"AEDFXA1EN018" 21463 20820 21184 30.733946
"AEDFXA1EN018" 21701 20820 21184 30.733946
"AEDFXA1EN018" 21414 20820 21184 30.733946
"AEDFXA1EN018" 21680 20820 21184 30.733946
"AEDFXA1EN018" 21421 20820 21184 30.733946
"AEDFXA1EN018" 21603 20820 21184 30.733946
"AEDFXA1EN018" 21505 20820 21184 30.733946
"AEDFXA1EN018" 21848 20820 21184 30.733946
"AEDFXA1EN018" 21841 20820 21184 30.733946
"AEDFXA1EN018" 21435 20820 21184 30.733946
"AEDFXA1EN018" 21568 20820 21184 30.733946
"AEDFXA1EN018" 21582 20820 21184 30.733946
"AEDFXA1EN018" 21771 20820 21184 30.733946
"AEDFXA1EN018" 21764 20820 21184 30.733946
"AEDFXA1EN018" 21652 20820 21184 30.733946
"AEDFXA1EN018" 21778 20820 21184 30.733946
"AEDFXA1EN018" 21820 20820 21184 30.733946
"AEDFXA1EN018" 21659 20820 21184 30.733946
"AEDFXA1EN018" 21456 20820 21184 30.733946
"AEDFXA1EN018" 21827 20820 21184 30.733946
"AEDFXA1EN018" 21631 20820 21184 30.733946
"AEDFXA1EN018" 21596 20820 21184 30.733946
"AEDFXA1EN018" 21792 20820 21184 30.733946
"AEDFXA1EN018" 21743 20820 21184 30.733946
"AEDFXA1EN018" 21442 20820 21184 30.733946
"AEDFXA1EN018" 21449 20820 21184 30.733946
"AEDFXA1EN018" 21638 20820 21184 30.733946
"AEDFXA1EN018" 21715 20820 21184 30.733946
"AEDFXA1EN018" 21512 20820 21184 30.733946
"AEDFXA1EN018" 21477 20820 21184 30.733946
"AEDFXA1EN018" 21799 20820 21184 30.733946
"AEDFXA1EN018" 21869 20820 21184 30.733946
"AEDFXA1EN018" 21722 20820 21184 30.733946
"AEDFXA1EN018" 21526 20820 21184 30.733946
"AEDFXA1EN018" 21393 20820 21184 30.733946
"AEDFXA1EN018" 21610 20820 21184 30.733946
"AEDFXA1EN018" 21484 20820 21184 30.733946
"AEDFXA1EN018" 21834 20820 21184 30.733946
"AEDFXA1EN018" 21708 20820 21184 30.733946
"AEDFXA1EN018" 21645 20820 21184 30.733946
"AEDFXA1EN018" 21498 20820 21184 30.733946
"AEDFXA1EN018" 21806 20820 21184 30.733946
"AEDFXA1EN018" 21750 20820 21184 30.733946
"AEDFXA1EN018" 21561 20820 21184 30.733946
"AEDFXA1EN018" 21554 20820 21184 30.733946
"AEDFXA1EN018" 21491 20820 21184 30.733946
"AEDFXA1EN018" 21540 20820 21184 30.733946
"AEDFXA1EN018" 21400 20820 21184 30.733946
"AEDFXA1EN018" 21785 20820 21184 30.733946
"AEDFXA1EN018" 21729 20820 21184 30.733946
"AEDFXA1EN018" 21589 20820 21184 30.733946
"AEDFXA1EN018" 21547 20820 21184 30.733946
"AEDFXA1EN018" 21757 20820 21184 30.733946
"AEDFXA1EN018" 21855 20820 21184 30.733946
"AEDFXA1EN018" 21407 20820 21184 30.733946
"AEDFXA1EN018" 21673 20820 21184 30.733946
"AEDFXA1EN018" 22485 21550 21914    .00391
"AEDFXA1EN018" 22296 21550 21914    .00391
"AEDFXA1EN018" 22527 21550 21914    .00391
"AEDFXA1EN018" 22548 21550 21914    .00391
"AEDFXA1EN018" 22415 21550 21914    .00391
"AEDFXA1EN018" 22373 21550 21914    .00391
"AEDFXA1EN018" 22492 21550 21914    .00391
"AEDFXA1EN018" 22541 21550 21914    .00391
"AEDFXA1EN018" 22520 21550 21914    .00391
"AEDFXA1EN018" 22359 21550 21914    .00391
"AEDFXA1EN018" 22436 21550 21914    .00391
"AEDFXA1EN018" 22443 21550 21914    .00391
"AEDFXA1EN018" 22394 21550 21914    .00391
"AEDFXA1EN018" 22303 21550 21914    .00391
"AEDFXA1EN018" 22506 21550 21914    .00391
"AEDFXA1EN018" 22352 21550 21914    .00391
"AEDFXA1EN018" 22513 21550 21914    .00391
"AEDFXA1EN018" 22324 21550 21914    .00391
"AEDFXA1EN018" 22331 21550 21914    .00391
"AEDFXA1EN018" 22464 21550 21914    .00391
"AEDFXA1EN018" 22380 21550 21914    .00391
"AEDFXA1EN018" 22555 21550 21914    .00391
"AEDFXA1EN018" 22408 21550 21914    .00391
"AEDFXA1EN018" 22345 21550 21914    .00391
"AEDFXA1EN018" 22366 21550 21914    .00391
"AEDFXA1EN018" 22450 21550 21914    .00391
"AEDFXA1EN018" 22499 21550 21914    .00391
"AEDFXA1EN018" 22478 21550 21914    .00391
"AEDFXA1EN018" 22387 21550 21914    .00391
"AEDFXA1EN018" 22422 21550 21914    .00391
"AEDFXA1EN018" 22338 21550 21914    .00391
"AEDFXA1EN018" 22401 21550 21914    .00391
"AEDFXA1EN018" 22429 21550 21914    .00391
end
format %d daily_filedate
format %td reporting_date_start
format %td reporting_date_end

encode isin, g(id)
preserve
keep id
contract id, freq(daily_filedate)
expand 2
bys id: replace daily_filedate= cond(_n==1, td(01jan2018), td(11dec2021))
xtset id daily_filedate
tsfill
tempfile dates
save `dates'
restore
merge 1:1 id daily_filedate using `dates', nogen
gen adnt_new= adnt_old
bys id (daily_filedate): replace adnt_new= adnt_new[_n-1] if missing(adnt_new)
gsort id -daily_filedate
by id: replace adnt_new= adnt_new[_n-1] if missing(adnt_new)
drop if missing( daily_filedate )
sort id daily_filedate

Res.:

. l id daily_filedate adnt_new, sepby(id)

      +-------------------------------------+
      |           id   daily_f~e   adnt_new |
      |-------------------------------------|
   1. | AEDFXA1EN018   01jan2018   30.73395 |
   2. | AEDFXA1EN018   02jan2018   30.73395 |
   3. | AEDFXA1EN018   03jan2018   30.73395 |
   4. | AEDFXA1EN018   04jan2018   30.73395 |
   5. | AEDFXA1EN018   05jan2018   30.73395 |
   6. | AEDFXA1EN018   06jan2018   30.73395 |
   7. | AEDFXA1EN018   07jan2018   30.73395 |
   8. | AEDFXA1EN018   08jan2018   30.73395 |
   9. | AEDFXA1EN018   09jan2018   30.73395 |
  10. | AEDFXA1EN018   10jan2018   30.73395 |
  11. | AEDFXA1EN018   11jan2018   30.73395 |
  12. | AEDFXA1EN018   12jan2018   30.73395 |
  13. | AEDFXA1EN018   13jan2018   30.73395 |
  14. | AEDFXA1EN018   14jan2018   30.73395 |
  15. | AEDFXA1EN018   15jan2018   30.73395 |
  16. | AEDFXA1EN018   16jan2018   30.73395 |
  17. | AEDFXA1EN018   17jan2018   30.73395 |
  18. | AEDFXA1EN018   18jan2018   30.73395 |
  19. | AEDFXA1EN018   19jan2018   30.73395 |
  20. | AEDFXA1EN018   20jan2018   30.73395 |
  21. | AEDFXA1EN018   21jan2018   30.73395 |
  22. | AEDFXA1EN018   22jan2018   30.73395 |
  23. | AEDFXA1EN018   23jan2018   30.73395 |
  24. | AEDFXA1EN018   24jan2018   30.73395 |
  25. | AEDFXA1EN018   25jan2018   30.73395 |
  26. | AEDFXA1EN018   26jan2018   30.73395 |
  27. | AEDFXA1EN018   27jan2018   30.73395 |
  28. | AEDFXA1EN018   28jan2018   30.73395 |
  29. | AEDFXA1EN018   29jan2018   30.73395 |
  30. | AEDFXA1EN018   30jan2018   30.73395 |
  31. | AEDFXA1EN018   31jan2018   30.73395 |
  32. | AEDFXA1EN018   01feb2018   30.73395 |
  33. | AEDFXA1EN018   02feb2018   30.73395 |
  34. | AEDFXA1EN018   03feb2018   30.73395 |
  35. | AEDFXA1EN018   04feb2018   30.73395 |
  36. | AEDFXA1EN018   05feb2018   30.73395 |
  37. | AEDFXA1EN018   06feb2018   30.73395 |
  38. | AEDFXA1EN018   07feb2018   30.73395 |
  39. | AEDFXA1EN018   08feb2018   30.73395 |
  40. | AEDFXA1EN018   09feb2018   30.73395 |
  41. | AEDFXA1EN018   10feb2018   30.73395 |
  42. | AEDFXA1EN018   11feb2018   30.73395 |
  43. | AEDFXA1EN018   12feb2018   30.73395 |
 

1389. | AEDFXA1EN018   20oct2021     .00391 |
1390. | AEDFXA1EN018   21oct2021     .00391 |
1391. | AEDFXA1EN018   22oct2021     .00391 |
1392. | AEDFXA1EN018   23oct2021     .00391 |
1393. | AEDFXA1EN018   24oct2021     .00391 |
1394. | AEDFXA1EN018   25oct2021     .00391 |
1395. | AEDFXA1EN018   26oct2021     .00391 |
1396. | AEDFXA1EN018   27oct2021     .00391 |
1397. | AEDFXA1EN018   28oct2021     .00391 |
1398. | AEDFXA1EN018   29oct2021     .00391 |
1399. | AEDFXA1EN018   30oct2021     .00391 |
1400. | AEDFXA1EN018   31oct2021     .00391 |
1401. | AEDFXA1EN018   01nov2021     .00391 |
1402. | AEDFXA1EN018   02nov2021     .00391 |
1403. | AEDFXA1EN018   03nov2021     .00391 |
1404. | AEDFXA1EN018   04nov2021     .00391 |
1405. | AEDFXA1EN018   05nov2021     .00391 |
1406. | AEDFXA1EN018   06nov2021     .00391 |
1407. | AEDFXA1EN018   07nov2021     .00391 |
1408. | AEDFXA1EN018   08nov2021     .00391 |
1409. | AEDFXA1EN018   09nov2021     .00391 |
1410. | AEDFXA1EN018   10nov2021     .00391 |
1411. | AEDFXA1EN018   11nov2021     .00391 |
1412. | AEDFXA1EN018   12nov2021     .00391 |
1413. | AEDFXA1EN018   13nov2021     .00391 |
1414. | AEDFXA1EN018   14nov2021     .00391 |
1415. | AEDFXA1EN018   15nov2021     .00391 |
1416. | AEDFXA1EN018   16nov2021     .00391 |
1417. | AEDFXA1EN018   17nov2021     .00391 |
1418. | AEDFXA1EN018   18nov2021     .00391 |
1419. | AEDFXA1EN018   19nov2021     .00391 |
1420. | AEDFXA1EN018   20nov2021     .00391 |
1421. | AEDFXA1EN018   21nov2021     .00391 |
1422. | AEDFXA1EN018   22nov2021     .00391 |
1423. | AEDFXA1EN018   23nov2021     .00391 |
1424. | AEDFXA1EN018   24nov2021     .00391 |
1425. | AEDFXA1EN018   25nov2021     .00391 |
1426. | AEDFXA1EN018   26nov2021     .00391 |
1427. | AEDFXA1EN018   27nov2021     .00391 |
1428. | AEDFXA1EN018   28nov2021     .00391 |
1429. | AEDFXA1EN018   29nov2021     .00391 |
1430. | AEDFXA1EN018   30nov2021     .00391 |
1431. | AEDFXA1EN018   01dec2021     .00391 |
1432. | AEDFXA1EN018   02dec2021     .00391 |
1433. | AEDFXA1EN018   03dec2021     .00391 |
1434. | AEDFXA1EN018   04dec2021     .00391 |
1435. | AEDFXA1EN018   05dec2021     .00391 |
1436. | AEDFXA1EN018   06dec2021     .00391 |
1437. | AEDFXA1EN018   07dec2021     .00391 |
1438. | AEDFXA1EN018   08dec2021     .00391 |
1439. | AEDFXA1EN018   09dec2021     .00391 |
1440. | AEDFXA1EN018   10dec2021     .00391 |
1441. | AEDFXA1EN018   11dec2021     .00391 |
      +-------------------------------------+

.

[Marco Errico]

Dear Andrew Musau

Truly thank you for your time and taking care the issue.
I looked carefully to you codes and I tried to implement it.

Unfortunately stata gives me this error message

. merge 1:1 id daily_filedate using `dates', nogen
variables id daily_filedate do not uniquely identify observations in the
    master data
r(459);

I tried to understand what could be the source of the problem but don't fully get it.

I think that one of the reason might be the same id and daily_file_date may contain duplicates because of different reporting periods.
For instance the id X uploaded today may contain information regarding adnt of 2017, 2018 and 2019 reporting period.

Do you have any advice on how to tackle the issue?
Many thanks again.

[Andrew Musau]

Try either

merge m:1

or

merge 1:m

[Marco Errico]

Thanks Andrew Musau

I see that your solution is indeed really helpful and going in the direction on how I'm wishing the panel.
Truly thank you.

Just few things which I see now that I'm exploring the data.
I attached a screenshoot.

As you can see, the id is correcting capturing the adnt_new based on the reportin period. When reporting period is in the year 2017, the value in the adnt_old will be contained in the adnt_new.
Then, I see that on 2 March 2019 there is for the first time the reporting period 2018. And I also see that adnt_new is correctly changing in according the rules in #1.
So everything good, so far.

Nevertheless, if you see on 23march2019, the adnt_new is wrongly changing. Indeed, it can only change if adnt_old is changing, but with two condition. 1) reporting period is in year 2018; 2) daily_filedate between 1^st April 2019 to 31th March 2020.
In this case adnt_new doesn't have to consider the change on adnt_old, because the reporting period is 2017.

It would have considered a change in case reporting period 2018 and daily_file_date between april 2019 - march 2020.

My apologies in case in post #1 I didn't better clarify the rules which I wish to have the panel. I tried all of my best to create less confusion as possible.

I hope you have some guidance on that.

Thanks again

[Andrew Musau]

You have a definition of the mapping. So as I stated in #2, the calendar year spans the months January- December, but you have some fiscal year that spans April- March. So define a new time variable and with this, you can use the lag operator to create the wanted variable.

[Marco Errico]

Hi Andrew Musau

Thank you again.
I tried to implemented what you suggested but I got stuck many times.
I explored other solution and seems that from #1 I can get the correct values of adnt_new with this codes

gen adnt_new=.
bys isin: replace adnt_new= adnt_old if reporting_date_start>=td(01jan2017) & reporting_date_end<=td(31dec2017) & filedate==""
bys isin: replace adnt_new= adnt_old if reporting_date_start>=td(01jan2018) & reporting_date_end<=td(31dec2018) & inrange(daily_filedate, td(01april2019), td(31march2020))
bys isin: replace adnt_new= adnt_old if reporting_date_start>=td(01jan2019) & reporting_date_end<=td(31dec2019) & inrange(daily_filedate, td(01april2020), td(31march2021))
bys isin: replace adnt_new= adnt_old if reporting_date_start>=td(01jan2020) & reporting_date_end<=td(31dec2020) & inrange(daily_filedate, td(01april2021), td(01october2021))
replace daily_filedate=td(01jan2018) if filedate==""

Now from here I am trying to create a new dataset, that for each ISIN and a new time dimension variable (that start from daily_filedate to today) will capture and carryforward the value of adnt_new.
I will try to take advantage of your codes on creating this dataset.

Thanks again