Variable takes value of the lower range number

[Fahad Mirza]

Hello everyone.

I am looking to generate a variable such that if its value falls between 2 consecutive variables, then it should take the value of the first one from which it is greater than.

Dataset:

* Example generated by -dataex-. To install: ssc install dataex
clear
input byte id int(first_attempt date_batch1 date_batch2 date_batch3 date_batch4 date_batch5 date_batch6 date_batch7 date_batch8 date_batch9 date_batch10 date_batch11 date_batch12 date_batch13 date_batch14)
1 22652 22457 22487 22518 22539 22629 22683 22707 22740 22775 22818 22847     .     .     .
1 22672 22457 22487 22518 22539 22629 22683 22707 22740 22775 22818 22847     .     .     .
1 22685 22457 22487 22518 22539 22629 22683 22707 22740 22775 22818 22847     .     .     .
1 22707 22457 22487 22518 22539 22629 22683 22707 22740 22775 22818 22847     .     .     .
2 22652 22602 22740     .     .     .     .     .     .     .     .     .     .     .     .
2 22652 22602 22740     .     .     .     .     .     .     .     .     .     .     .     .
2 22652 22602 22740     .     .     .     .     .     .     .     .     .     .     .     .
2 22789 22602 22740     .     .     .     .     .     .     .     .     .     .     .     .
2 22828 22602 22740     .     .     .     .     .     .     .     .     .     .     .     .
3 22652 22487 22539 22602 22683 22707 22740 22775 22818 22847     .     .     .     .     .
3 22672 22487 22539 22602 22683 22707 22740 22775 22818 22847     .     .     .     .     .
4 22652 22457 22487 22518 22539 22573 22602 22629 22658 22683 22707 22740 22775 22818 22847
4 22672 22457 22487 22518 22539 22573 22602 22629 22658 22683 22707 22740 22775 22818 22847
4 22685 22457 22487 22518 22539 22573 22602 22629 22658 22683 22707 22740 22775 22818 22847
4 22707 22457 22487 22518 22539 22573 22602 22629 22658 22683 22707 22740 22775 22818 22847
4 22830 22457 22487 22518 22539 22573 22602 22629 22658 22683 22707 22740 22775 22818 22847
5 22652 22457 22487 22518 22539 22573 22602 22629 22658 22683 22707 22740 22775 22818 22847
5 22685 22457 22487 22518 22539 22573 22602 22629 22658 22683 22707 22740 22775 22818 22847
5 22707 22457 22487 22518 22539 22573 22602 22629 22658 22683 22707 22740 22775 22818 22847
5 22851 22457 22487 22518 22539 22573 22602 22629 22658 22683 22707 22740 22775 22818 22847
end
format %td first_attempt
format %td date_batch1
format %td date_batch2
format %td date_batch3
format %td date_batch4
format %td date_batch5
format %td date_batch6
format %td date_batch7
format %td date_batch8
format %td date_batch9
format %td date_batch10
format %td date_batch11
format %td date_batch12
format %td date_batch13
format %td date_batch14

For example:

If for ID 1, if the date 07 Jan 2022, falls between variable date_batch5 and date_batch6 then a new variable will take the value of date_batch5 which is 15 Dec 2021

[Fahad Mirza]

I think I have found a solution and it was pretty basic

    generate n_date_batch = .
    
    foreach var of varlist date_batch* {
    
            replace n_date_batch = `var' if !missing(`var') & first_attempt >= `var'
        
        
    }