Announcement

Collapse
No announcement yet.
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Figuring out if a case is within a range

    Hi all:

    I have data that indicates the time people were booked (BookingDateTime) into and released (ReleaseDateTime) from jail. Both variables are double %tc.

    I need to create a variable that indicates if they were present in a given quarter. People can be in multiple quarters (e.g. if they were in jail for 2 years). I'd want to know that. My idea would be to create variables called q1, q2, q3 -- and then give each person a 1 or a 0 based on whether they were booked in during that quarter at all or not. My quarters are a little wonky, given our reporting. But if you just figure Jan 1 -March 31, I can adjust.

    For example, someone could be booked in on 1oct2014 12:31:42 and released on 15jan2019 01:11:15
    This person should get a 1 for all quarters between those two dates.

    My initial thought was to
    Code:
    gen quarter = .
    replace quarter = 0 if BookingDateTime <=tc(31jan2018 23:59:59)
    replace quarter = 1 if BookingDateTime >=tc(01feb2018 00:00:00) & BookingDateTime <= tc(31apr2018 23:59:59)
    replace quarter = 2 if BookingDateTime >=tc(01may2018 00:00:00) & BookingDateTime <= tc(31jul2018 23:59:59)
    ...
    The issue here is that this makes the quarters mutually exclusive. Someone is either in a quarter or not based on BookingDateTime. While this would accurately place them within the first quarter they were in jail, it would not indicate if they were in jail subsequent quarters.

    I then thought to do the similar above but also use | ReleaseDateTime. But that caused similar issues.

    What I need to know is if their time range overlaps with a quarter.

    I am trying to get this to work: https://www.stata.com/statalist/arch.../msg01004.html given my different time format, and having no luck so far. However, I will keep trying.


    Code:
    * Example generated by -dataex-. To install: ssc install dataex
    clear
    input double(BookingDateTime ReleaseDateTime)
    1706819759999.9998 1849523760000.0002
         1720915380000 1839846059999.9998
    1758474059999.9998      1862125020000
    1767658139999.9998      1.8372657e+12
    1769427779999.9998 1845648120000.0002
    1771614179999.9998 1845295800000.0002
    1772040720000.0002 1842602039999.9998
         1774294980000 1837502400000.0002
         1775503980000 1842207360000.0002
    1780100040000.0002      1903910399000
    1781891519999.9998      1882441320000
         1781999301000 1825250876000.0002
    1784918179999.9998 1834787195999.9998
    1785945779999.9998 1833801924000.0002
    1785952260000.0002 1834312719999.9998
    1786562536000.0002      1828704544000
         1786952682000 1828176272999.9998
    1791228539999.9998 1863442260000.0002
         1791813180000 1833794464000.0002
    1792341240000.0002      1842876120000
         1793027682000 1829471546000.0002
         1793466540000      1849793940000
    1794891253000.0002 1832967565999.9998
    1795292340000.0002      1903910399000
    1796763060000.0002      1.8749151e+12
    1797502833999.9998      1825321137000
    1798843956999.9998 1832411671999.9998
    1799121263000.0002 1832411790000.0002
    1799492771000.0002 1831902490000.0002
    1799680835999.9998 1828704477000.0002
         1.7999448e+12 1856769119999.9998
    1800742785000.0002      1828705487000
    1800804761000.0002 1831936479999.9998
    1802271695999.9998 1830865593000.0002
         1802314620000 1834312638000.0002
         1802351847000 1827572411999.9998
    1802636284999.9998 1827566621999.9998
    1802955704999.9998      1825339365000
         1802959527000      1826096494000
         1803731340000 1836197999999.9998
         1804192524000 1826984970000.0002
         1804342860000 1843398060000.0002
    1804610382999.9998 1826536495999.9998
    1804776066999.9998 1831382654999.9998
         1805141668000      1825236673000
    1805211024999.9998 1825836601999.9998
    1805494320000.0002 1846250400000.0002
         1805765217000      1826377082000
         1805895304000 1829228506999.9998
    1806505833999.9998 1831359255000.0002
         1806589602000 1826883435000.0002
    1806686682999.9998 1827661562999.9998
    1806792600000.0002      1836221760000
    1806854940000.0002 1844372639999.9998
    1806950636000.0002 1831038224999.9998
    1806952648999.9998 1829472046999.9998
    1807039080000.0002      1.8380391e+12
    1807191993999.9998      1830177797000
         1807195548000 1833001702000.0002
    1807481189000.0002      1828771385000
    1807490999999.9998 1833549915000.0002
    1807543020999.9998 1827587280000.0002
    1808219459999.9998      1865151720000
         1808324478000      1834214025000
         1808952960000 1838638560000.0002
    1808953219999.9998 1828950314999.9998
    1809349979999.9998 1837848839999.9998
    1810149539999.9998 1839243900000.0002
    1810229897000.0002 1828099634000.0002
         1810329704000      1826984789000
    1810340874000.0002 1829465853000.0002
         1810357126000 1831994481000.0002
         1810373983000      1826313051000
         1810462997000 1828705327000.0002
         1810514678000      1827418327000
         1811004199000 1829817256000.0002
         1811061022000 1825699592999.9998
    1811194047000.0002      1831278456000
         1.8113598e+12 1834210613999.9998
         1811623572000      1830765673000
    1811649080000.0002      1825744058000
    1811768730999.9998      1825321234000
    1811806358999.9998 1827394410999.9998
    1812069503999.9998      1825324372000
         1812297840000      1836222540000
    1812313323999.9998 1826394487999.9998
    1812361875000.0002 1827907375999.9998
    1812488557000.0002 1827522383000.0002
    1812627900000.0002      1836657060000
         1812642660000      1860942420000
         1812848326000      1825693465000
    1813079316000.0002 1826878446999.9998
    1813236465000.0002      1825671077000
         1813291637000      1828184966000
         1813379841000 1825171038999.9998
    1813534920999.9998 1826299979000.0002
         1813607093000 1826983052999.9998
         1813694668000 1831043548999.9998
         1813871862000      1825580135000
         1813961263000      1828176185000
    end
    format %tc BookingDateTime
    format %tc ReleaseDateTime

    I really appreciate any help you can provide.
    Last edited by Bridget Diamond-Welch; 26 May 2020, 08:52.

  • #2
    hi you may want to use these command below to create quarter for booking date time and release date time:

    gen qbook = qofd(dofc(BookingDateTime))
    gen qrelea = qofd(dofc(ReleaseDateTime))
    format qbook qrelea %tq

    you can substract qrelea to qbook to see how many quarter a participant gone through.
    but which quarter in which year is very difference in interpretation

    pardon me for potential typos.

    Comment

    Working...
    X