Generating Count Variable for 3 Previous Years When There are Multiple Observations per Year

[Christelle Alkhoury]

Hello,

Please I need help generating a variable that counts the number of unique clients over the previous 3 years (Clients_Pre). I have the TotalClients_Unique which represents the number of unique clients per year. It is missing for some observations because I coded it to show only the first value for each AuditOffice1-Year from the total_clients variable.

* Example generated by -dataex-. To install: ssc install dataex
clear
input float Year str77 AuditOffice1 float(total_clients TotalClients_Unique)
2010 "ABBM Group Ltd LLP_Houston"          1  1
2019 "ACM LLP (CO)_Denver"                 1  1
2018 "ACM LLP (CO)_Greeley"                1  1
2019 "ACM LLP (CO)_Greeley"                1  1
2020 "ADKF PC_San Antonio"                 2  2
2020 "ADKF PC_San Antonio"                 2  .
2021 "ADKF PC_San Antonio"                 2  2
2021 "ADKF PC_San Antonio"                 2  .
2022 "ADKF PC_San Antonio"                 2  2
2022 "ADKF PC_San Antonio"                 2  .
2023 "ADKF PC_San Antonio"                 1  1
2016 "AJ Robbins CPA LLC (Active)_Denver"  1  1
2017 "AJ Robbins CPA LLC (Active)_Denver"  4  4
2017 "AJ Robbins CPA LLC (Active)_Denver"  4  .
2017 "AJ Robbins CPA LLC (Active)_Denver"  4  .
2017 "AJ Robbins CPA LLC (Active)_Denver"  4  .
2018 "AJ Robbins CPA LLC (Active)_Denver"  2  2
2018 "AJ Robbins CPA LLC (Active)_Denver"  2  .
2019 "AJ Robbins CPA LLC (Active)_Denver"  4  4
2019 "AJ Robbins CPA LLC (Active)_Denver"  4  .
2019 "AJ Robbins CPA LLC (Active)_Denver"  4  .
2019 "AJ Robbins CPA LLC (Active)_Denver"  4  .
2020 "AJ Robbins CPA LLC (Active)_Denver"  4  4
2020 "AJ Robbins CPA LLC (Active)_Denver"  4  .
2020 "AJ Robbins CPA LLC (Active)_Denver"  4  .
2020 "AJ Robbins CPA LLC (Active)_Denver"  4  .
2016 "AMC Auditing LLC_Las Vegas"          6  6
2016 "AMC Auditing LLC_Las Vegas"          6  .
2016 "AMC Auditing LLC_Las Vegas"          6  .
2016 "AMC Auditing LLC_Las Vegas"          6  .
2016 "AMC Auditing LLC_Las Vegas"          6  .
2016 "AMC Auditing LLC_Las Vegas"          6  .
2017 "AMC Auditing LLC_Las Vegas"         13 13
2017 "AMC Auditing LLC_Las Vegas"         13  .
2017 "AMC Auditing LLC_Las Vegas"         13  .
2017 "AMC Auditing LLC_Las Vegas"         13  .
2017 "AMC Auditing LLC_Las Vegas"         13  .
2017 "AMC Auditing LLC_Las Vegas"         13  .
2017 "AMC Auditing LLC_Las Vegas"         13  .
2017 "AMC Auditing LLC_Las Vegas"         13  .
2017 "AMC Auditing LLC_Las Vegas"         13  .
2017 "AMC Auditing LLC_Las Vegas"         13  .
2017 "AMC Auditing LLC_Las Vegas"         13  .
2017 "AMC Auditing LLC_Las Vegas"         13  .
2017 "AMC Auditing LLC_Las Vegas"         13  .
2018 "AMC Auditing LLC_Las Vegas"         10 10
2018 "AMC Auditing LLC_Las Vegas"         10  .
2018 "AMC Auditing LLC_Las Vegas"         10  .
2018 "AMC Auditing LLC_Las Vegas"         10  .
2018 "AMC Auditing LLC_Las Vegas"         10  .
2018 "AMC Auditing LLC_Las Vegas"         10  .
2018 "AMC Auditing LLC_Las Vegas"         10  .
2018 "AMC Auditing LLC_Las Vegas"         10  .
2018 "AMC Auditing LLC_Las Vegas"         10  .
2018 "AMC Auditing LLC_Las Vegas"         10  .
2010 "Accell Audit & Compliance PA_Tampa"  1  1
2011 "Accell Audit & Compliance PA_Tampa"  2  2
2011 "Accell Audit & Compliance PA_Tampa"  2  .
2012 "Accell Audit & Compliance PA_Tampa"  2  2
2012 "Accell Audit & Compliance PA_Tampa"  2  .
2013 "Accell Audit & Compliance PA_Tampa"  3  3
2013 "Accell Audit & Compliance PA_Tampa"  3  .
2013 "Accell Audit & Compliance PA_Tampa"  3  .
2014 "Accell Audit & Compliance PA_Tampa"  3  3
2014 "Accell Audit & Compliance PA_Tampa"  3  .
2014 "Accell Audit & Compliance PA_Tampa"  3  .
2015 "Accell Audit & Compliance PA_Tampa"  2  2
2015 "Accell Audit & Compliance PA_Tampa"  2  .
2016 "Accell Audit & Compliance PA_Tampa"  3  3
2016 "Accell Audit & Compliance PA_Tampa"  3  .
2016 "Accell Audit & Compliance PA_Tampa"  3  .
2017 "Accell Audit & Compliance PA_Tampa"  3  3
2017 "Accell Audit & Compliance PA_Tampa"  3  .
2017 "Accell Audit & Compliance PA_Tampa"  3  .
2018 "Accell Audit & Compliance PA_Tampa"  3  3
2018 "Accell Audit & Compliance PA_Tampa"  3  .
2018 "Accell Audit & Compliance PA_Tampa"  3  .
2019 "Accell Audit & Compliance PA_Tampa"  4  4
2019 "Accell Audit & Compliance PA_Tampa"  4  .
2019 "Accell Audit & Compliance PA_Tampa"  4  .
2019 "Accell Audit & Compliance PA_Tampa"  4  .
2020 "Accell Audit & Compliance PA_Tampa"  7  7
2020 "Accell Audit & Compliance PA_Tampa"  7  .
2020 "Accell Audit & Compliance PA_Tampa"  7  .
2020 "Accell Audit & Compliance PA_Tampa"  7  .
2020 "Accell Audit & Compliance PA_Tampa"  7  .
2020 "Accell Audit & Compliance PA_Tampa"  7  .
2020 "Accell Audit & Compliance PA_Tampa"  7  .
2021 "Accell Audit & Compliance PA_Tampa" 14 14
2021 "Accell Audit & Compliance PA_Tampa" 14  .
2021 "Accell Audit & Compliance PA_Tampa" 14  .
2021 "Accell Audit & Compliance PA_Tampa" 14  .
2021 "Accell Audit & Compliance PA_Tampa" 14  .
2021 "Accell Audit & Compliance PA_Tampa" 14  .
2021 "Accell Audit & Compliance PA_Tampa" 14  .
2021 "Accell Audit & Compliance PA_Tampa" 14  .
2021 "Accell Audit & Compliance PA_Tampa" 14  .
2021 "Accell Audit & Compliance PA_Tampa" 14  .
2021 "Accell Audit & Compliance PA_Tampa" 14  .
2021 "Accell Audit & Compliance PA_Tampa" 14  .
end

So what I want is for Clients_Pre to be for example: for ADKF PC_San Antonio in 2023 the value should equal the sum of the TotalClients_Unique of the prior 3 years which here is = 2 (in 2022) + 2 (in 2021) + 2 (in 2020) = 6 --> Clients_Pre=6
Another example: AJ Robbins CPA LLC (Active)_Denver in 2019 would have Clients_Pre=7 (which is 1+4+2 for 2016, 2017 and 2018).

Thank you!

[Nick Cox]

.

 rangestat (sum) Clients_Pre=TotalClients_Unique, interval(Year -3 -1) by(AuditOffice1)

. 
. tabdisp AuditOffice1 Year, c(TotalClients_Unique Clients_Pre)

----------------------------------------------------------------------------------------------------------
                                   |                                 Year                                 
                      AuditOffice1 | 2010 2011 2012 2013 2014 2015 2016 2017 2018 2019 2020 2021 2022 2023
-----------------------------------+----------------------------------------------------------------------
        ABBM Group Ltd LLP_Houston |    1                                                                 
                                   |                                                                      
                                   | 
               ACM LLP (CO)_Denver |                                                 1                    
                                   |                                                                      
                                   | 
              ACM LLP (CO)_Greeley |                                            1    1                    
                                   |                                                 1                    
                                   | 
               ADKF PC_San Antonio |                                                      2    2    2    1
                                   |                                                           2    4    6
                                   | 
AJ Robbins CPA LLC (Active)_Denver |                                  1    4    2    4    4               
                                   |                                       1    5    7   10               
                                   | 
        AMC Auditing LLC_Las Vegas |                                  6   13   10                         
                                   |                                       6   19                         
                                   | 
Accell Audit & Compliance PA_Tampa |    1    2    2    3    3    2    3    3    3    4    7   14          
                                   |         1    3    5    7    8    8    8    8    9   10   14          
----------------------------------------------------------------------------------------------------------

rangestat is from SSC. Search the forum to see other examples.

[Christelle Alkhoury]

Thank you Nick for your swift response. It worked