Announcement

Collapse
No announcement yet.
X
Collapse
  • Page of 1
  • Filter
  • Time
  • Show
Clear All
new posts
  • #1

    Adding more observations to a variable year

    Hello,

    I am currently trying to add the year 2021 and 2022 to my dataset. Currently, I have observations for the years 2018-2020, as I need data for the years 2018-2022, I would like to copy the values from the year 2020 to the years 2021 and 2022. My first step, was trying to create the years 2021 and 2022, as they dont exist in my dataset, but, I am failing to do so. The data is sorted by company codes like CUSIP, so per CUSIP I need to add the year 2021 and 2022 and the copied values from year 2020.
    How can I do this, ive tried using exapand, but I fail to assign it to the CUSIP.

    Thanks in Advance!
    Kim
    Tags: None
  • #2
    Code:
    gen expander = cond(year == 2020, 3, 1)
expand expander
by CUSIP (year), sort: replace year = year[1] + _n - 1
    • 1 like

    Comment

    • #3
      worked perfectly thanks a lot

      Comment

      • #4
        Clyde Schechter I used the code you provided again with a different set of data that I also wanted to extend for the same years as before (duplicate the results for 2020 to 2021 and 2022). However this time its not working,
        when applying the code, my results look the following way, I am assuming this might be due to the fact that for some firms I have multiple observations per year that I would like to keep. How do I need to adapt the code to my data?


        After using the code:
        Year CompanyName
        2018 The AES Corporation
        2019 The AES Corporation
        2020 The AES Corporation
        2021 The AES Corporation
        2022 The AES Corporation
        2018 AK Steel Holding Corporation
        2019 AK Steel Holding Corporation
        2018 AT&T Inc.
        2019 AT&T Inc.
        2020 AT&T Inc.
        2021 AT&T Inc.
        2022 AT&T Inc.
        2023 AT&T Inc.
        2024 AT&T Inc.
        2025 AT&T Inc.
        2026 AT&T Inc.
        2027 AT&T Inc.
        2028 AT&T Inc.
        2029 AT&T Inc.
        2030 AT&T Inc.
        2031 AT&T Inc.
        2032 AT&T Inc.
        2018 Abbott Laboratories
        2019 Abbott Laboratories
        2020 Abbott Laboratories
        2021 Abbott Laboratories
        2022 Abbott Laboratories
        2023 Abbott Laboratories
        2024 Abbott Laboratories
        2025 Abbott Laboratories
        2026 Abbott Laboratories
        2027 Abbott Laboratories
        2028 Abbott Laboratories
        2029 Abbott Laboratories
        2030 Abbott Laboratories
        2031 Abbott Laboratories
        2032 Abbott Laboratories
        2033 Abbott Laboratories
        2034 Abbott Laboratories
        2035 Abbott Laboratories
        2036 Abbott Laboratories
        2037 Abbott Laboratories
        2018 AbbVie Inc
        2019 AbbVie Inc
        2020 AbbVie Inc
        2021 AbbVie Inc
        2022 AbbVie Inc

        before using the code:
        Year CompanyName
        2018 AK Steel Holding Corporation
        2019 AK Steel Holding Corporation
        2018 AT&T Inc.
        2018 AT&T Inc.
        2018 AT&T Inc.
        2019 AT&T Inc.
        2019 AT&T Inc.
        2019 AT&T Inc.
        2020 AT&T Inc.
        2020 AT&T Inc.
        2020 AT&T Inc.
        2018 Abbott Laboratories
        2018 Abbott Laboratories
        2018 Abbott Laboratories
        2018 Abbott Laboratories
        2019 Abbott Laboratories
        2019 Abbott Laboratories
        2019 Abbott Laboratories
        2019 Abbott Laboratories
        2020 Abbott Laboratories
        2020 Abbott Laboratories
        2020 Abbott Laboratories
        2020 Abbott Laboratories
        2018 AbbVie Inc
        2019 AbbVie Inc
        2020 AbbVie Inc


        Thanks in advance!

        Comment

        • #5
          Code:
          * Example generated by -dataex-. For more info, type help dataex
clear
input int year str29 companyname
2018 "AK Steel Holding Corporation"
2019 "AK Steel Holding Corporation"
2018 "AT&T Inc."                  
2018 "AT&T Inc."                  
2018 "AT&T Inc."                  
2019 "AT&T Inc."                  
2019 "AT&T Inc."                  
2019 "AT&T Inc."                  
2020 "AT&T Inc."                  
2020 "AT&T Inc."                  
2020 "AT&T Inc."                  
2018 "Abbott Laboratories"        
2018 "Abbott Laboratories"        
2018 "Abbott Laboratories"        
2018 "Abbott Laboratories"        
2019 "Abbott Laboratories"        
2019 "Abbott Laboratories"        
2019 "Abbott Laboratories"        
2019 "Abbott Laboratories"        
2020 "Abbott Laboratories"        
2020 "Abbott Laboratories"        
2020 "Abbott Laboratories"        
2020 "Abbott Laboratories"        
2018 "AbbVie Inc"                  
2019 "AbbVie Inc"                  
2020 "AbbVie Inc"                  
end

preserve
keep companyname
duplicates drop
expand `=2022-2018+1'
by companyname, sort: gen year = 2018 + _n - 1
isid companyname year, sort
tempfile holding
save `holding'

restore
merge m:1 companyname year using `holding', assert(match using) nogenerate
sort companyname year
          In the future, when showing data examples, please use the -dataex- command to do so, as I have done here. If you are running version 18, 17, 16 or a fully updated version 15.1 or 14.2, -dataex- is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.

          Comment

          • #6
            Clyde Schechter thank you!
            Last edited by Kim Schwartau; 01 Dec 2023, 03:47.

            Comment

            • #7
              Clyde Schechter, I used your code, however, Im facing issues with the data (see below). I do have additional observations, but the observations are empty. I aiming for the observations of 2020 to be copied to 2021 and 2022. So firms where I only have observations from 2018 will continue to have only the observations from 2018 after the expansion. But for firms where I have data for 2020, I will have data for 2020, 2021 and 2022 after the expansion. Thanks in advance!

              Code:
              * Example generated by -dataex-. For more info, type help dataex
clear
input str6 CUSIP str95 CompanyName float Year str220 Type_ENG
"000886" "Royal Caribbean Cruises Ltd"    2018 "Compliance & onboarding"                                  
"000886" "Royal Caribbean Cruises Ltd"    2018 "Information collection (understanding supplier behavior)" 
"000886" "Royal Caribbean Cruises Ltd"    2019 "Information collection (understanding supplier behavior)" 
"000886" "Royal Caribbean Cruises Ltd"    2019 "Compliance & onboarding"                                  
"000886" "Royal Caribbean Cruises Ltd"    2020 "Compliance & onboarding"                                  
"000886" ""                               2021 ""                                                         
"000886" ""                               2022 ""                                                         
"000943" "LyondellBasell Industries N.V." 2018 "Innovation & collaboration (changing markets)"            
"000943" "LyondellBasell Industries N.V." 2019 "Innovation & collaboration (changing markets)"            
"000943" "LyondellBasell Industries N.V." 2020 "Compliance & onboarding"                                  
"000943" "LyondellBasell Industries N.V." 2020 "Innovation & collaboration (changing markets)"            
"000943" "LyondellBasell Industries N.V." 2020 "Information collection (understanding supplier behavior)" 
"000943" ""                               2021 ""                                                         
"000943" ""                               2022 ""                                                         
"000957" "ABM INDUSTRIES INC"             2018 "Compliance & onboarding"                                  
"000957" "ABM INDUSTRIES INC"             2019 "Compliance & onboarding"                                  
"000957" "ABM INDUSTRIES INC"             2019 "Information collection (understanding supplier behavior)" 
"000957" "ABM INDUSTRIES INC"             2020 "Information collection (understanding supplier behavior)" 
"000957" "ABM INDUSTRIES INC"             2020 "Compliance & onboarding"                                  
"000957" ""                               2021 ""                                                         
"000957" ""                               2022 ""                                                         
"001055" "AFLAC Incorporated"             2018 "Compliance & onboarding"                                  
"001055" "AFLAC Incorporated"             2019 "Compliance & onboarding"                                  
"001055" "AFLAC Incorporated"             2020 "Information collection (understanding supplier behavior)" 
"001055" "AFLAC Incorporated"             2020 "Compliance & onboarding"                                  
"001055" ""                               2021 ""                                                         
"001055" ""                               2022 ""                                                         
"00130H" "The AES Corporation"            2018 "Information collection (understanding supplier behavior)" 
"00130H" "The AES Corporation"            2019 "Information collection (understanding supplier behavior)" 
"00130H" "The AES Corporation"            2020 "Information collection (understanding supplier behavior)" 
"00130H" ""                               2021 ""                                                         
"00130H" ""                               2022 ""                                                         
"001547" "AK Steel Holding Corporation"   2018 "Compliance & onboarding"                                  
"001547" "AK Steel Holding Corporation"   2019 "Compliance & onboarding"                                  
"001547" ""                               2020 ""                                                         
"001547" ""                               2021 ""                                                         
"001547" ""                               2022 ""                                                         
"00206R" "AT&T Inc."                      2018 "Compliance & onboarding"                                  
"00206R" "AT&T Inc."                      2018 "Engagement & incentivization (changing supplier behavior)"
"00206R" "AT&T Inc."                      2018 "Information collection (understanding supplier behavior)" 
"00206R" "AT&T Inc."                      2019 "Compliance & onboarding"                                  
"00206R" "AT&T Inc."                      2019 "Information collection (understanding supplier behavior)" 
"00206R" "AT&T Inc."                      2019 "Engagement & incentivization (changing supplier behavior)"
"00206R" "AT&T Inc."                      2020 "Information collection (understanding supplier behavior)" 
"00206R" "AT&T Inc."                      2020 "Compliance & onboarding"                                  
"00206R" "AT&T Inc."                      2020 "Engagement & incentivization (changing supplier behavior)"
"00206R" ""                               2021 ""                                                         
"00206R" ""                               2022 ""                                                         
"002824" "Abbott Laboratories"            2018 "Compliance & onboarding"                                  
"002824" "Abbott Laboratories"            2018 "Innovation & collaboration (changing markets)"            
"002824" "Abbott Laboratories"            2018 "Information collection (understanding supplier behavior)" 
"002824" "Abbott Laboratories"            2019 "Compliance & onboarding"                                  
"002824" "Abbott Laboratories"            2019 "Innovation & collaboration (changing markets)"            
"002824" "Abbott Laboratories"            2019 "Information collection (understanding supplier behavior)" 
"002824" "Abbott Laboratories"            2020 "Information collection (understanding supplier behavior)" 
"002824" "Abbott Laboratories"            2020 "Innovation & collaboration (changing markets)"            
"002824" "Abbott Laboratories"            2020 "Compliance & onboarding"                                  
"002824" ""                               2021 ""                                                         
"002824" ""                               2022 ""                                                         
"00287Y" "AbbVie Inc"                     2018 "Information collection (understanding supplier behavior)" 
"00287Y" "AbbVie Inc"                     2019 "Information collection (understanding supplier behavior)" 
"00287Y" "AbbVie Inc"                     2020 "Information collection (understanding supplier behavior)" 
"00287Y" ""                               2021 ""                                                         
"00287Y" ""                               2022 ""                                                         
"002896" "Abercrombie & Fitch Co."        2018 "Information collection (understanding supplier behavior)" 
"002896" ""                               2019 ""                                                         
"002896" ""                               2020 ""                                                         
"002896" ""                               2021 ""                                                         
"002896" ""                               2022 ""                                                         
"004432" "Chubb Limited"                  2018 "Information collection (understanding supplier behavior)" 
"004432" "Chubb Limited"                  2019 "Information collection (understanding supplier behavior)" 
"004432" "Chubb Limited"                  2020 "Information collection (understanding supplier behavior)" 
"004432" ""                               2021 ""                                                         
"004432" ""                               2022 ""                                                         
"00508Y" ""                               2018 ""                                                         
"00508Y" "Acuity Brands Inc"              2019 "Information collection (understanding supplier behavior)" 
"00508Y" ""                               2020 ""                                                         
"00508Y" ""                               2021 ""                                                         
"00508Y" ""                               2022 ""                                                         
"00724F" "Adobe"                          2018 "Innovation & collaboration (changing markets)"            
"00724F" "Adobe"                          2019 "Innovation & collaboration (changing markets)"            
"00724F" "Adobe"                          2020 "Information collection (understanding supplier behavior)" 
"00724F" ""                               2021 ""                                                         
"00724F" ""                               2022 ""                                                         
"00738A" "ADTRAN INC"                     2018 "Engagement & incentivization (changing supplier behavior)"
"00738A" "ADTRAN INC"                     2019 "Information collection (understanding supplier behavior)" 
"00738A" "ADTRAN INC"                     2020 "Information collection (understanding supplier behavior)" 
"00738A" ""                               2021 ""                                                         
"00738A" ""                               2022 ""                                                         
"00751Y" ""                               2018 ""                                                         
"00751Y" "Advance Auto Parts Inc"         2019 "Information collection (understanding supplier behavior)" 
"00751Y" "Advance Auto Parts Inc"         2020 "Information collection (understanding supplier behavior)" 
"00751Y" ""                               2021 ""                                                         
"00751Y" ""                               2022 ""                                                         
"007903" "Advanced Micro Devices, Inc"    2018 "Compliance & onboarding"                                  
"007903" "Advanced Micro Devices, Inc"    2019 "Information collection (understanding supplier behavior)" 
"007903" "Advanced Micro Devices, Inc"    2020 "Information collection (understanding supplier behavior)" 
"007903" ""                               2021 ""                                                         
"007903" ""                               2022 ""                                                         
"00846U" "Agilent Technologies Inc."      2018 "Engagement & incentivization (changing supplier behavior)"
end
              Last edited by Kim Schwartau; 01 Dec 2023, 04:17.

              Comment

              • #8
                when adapting the code to:

                Code:
                preserve
keep CUSIP Type_ENG CompanyName
duplicates drop
expand `=2022-2018+1'
by CUSIP, sort: gen Year = 2018 + _n - 1
isid CUSIP Year, sort
tempfile holding
save `holding'

restore
merge m:1 CUSIP Year using `holding', assert(match using) nogenerate
sort CUSIP Year 

dataex Year CUSIP Type_ENG CompanyName

                my data looks like this:

                Code:
                * Example generated by -dataex-. For more info, type help dataex
clear
input float Year str6 CUSIP str220 Type_ENG str95 CompanyName
2018 "000886" "Information collection (understanding supplier behavior)"  "Royal Caribbean Cruises Ltd"   
2018 "000886" "Compliance & onboarding"                                   "Royal Caribbean Cruises Ltd"   
2019 "000886" "Information collection (understanding supplier behavior)"  "Royal Caribbean Cruises Ltd"   
2019 "000886" "Compliance & onboarding"                                   "Royal Caribbean Cruises Ltd"   
2020 "000886" "Compliance & onboarding"                                   "Royal Caribbean Cruises Ltd"   
2021 "000886" "Information collection (understanding supplier behavior)"  "Royal Caribbean Cruises Ltd"   
2022 "000886" "Compliance & onboarding"                                   "Royal Caribbean Cruises Ltd"   
2023 "000886" "Compliance & onboarding"                                   "Royal Caribbean Cruises Ltd"   
2024 "000886" "Information collection (understanding supplier behavior)"  "Royal Caribbean Cruises Ltd"   
2025 "000886" "Compliance & onboarding"                                   "Royal Caribbean Cruises Ltd"   
2026 "000886" "Compliance & onboarding"                                   "Royal Caribbean Cruises Ltd"   
2027 "000886" "Compliance & onboarding"                                   "Royal Caribbean Cruises Ltd"   
2018 "000943" "Innovation & collaboration (changing markets)"             "LyondellBasell Industries N.V."
2019 "000943" "Innovation & collaboration (changing markets)"             "LyondellBasell Industries N.V."
2020 "000943" "Compliance & onboarding"                                   "LyondellBasell Industries N.V."
2020 "000943" "Innovation & collaboration (changing markets)"             "LyondellBasell Industries N.V."
2020 "000943" "Information collection (understanding supplier behavior)"  "LyondellBasell Industries N.V."
2021 "000943" "Innovation & collaboration (changing markets)"             "LyondellBasell Industries N.V."
2022 "000943" "Innovation & collaboration (changing markets)"             "LyondellBasell Industries N.V."
2023 "000943" "Information collection (understanding supplier behavior)"  "LyondellBasell Industries N.V."
2024 "000943" "Information collection (understanding supplier behavior)"  "LyondellBasell Industries N.V."
2025 "000943" "Information collection (understanding supplier behavior)"  "LyondellBasell Industries N.V."
2026 "000943" "Innovation & collaboration (changing markets)"             "LyondellBasell Industries N.V."
2027 "000943" "Compliance & onboarding"                                   "LyondellBasell Industries N.V."
2028 "000943" "Innovation & collaboration (changing markets)"             "LyondellBasell Industries N.V."
2029 "000943" "Innovation & collaboration (changing markets)"             "LyondellBasell Industries N.V."
2030 "000943" "Information collection (understanding supplier behavior)"  "LyondellBasell Industries N.V."
2031 "000943" "Compliance & onboarding"                                   "LyondellBasell Industries N.V."
2032 "000943" "Compliance & onboarding"                                   "LyondellBasell Industries N.V."
2018 "000957" "Compliance & onboarding"                                   "ABM INDUSTRIES INC"            
2019 "000957" "Compliance & onboarding"                                   "ABM INDUSTRIES INC"            
2019 "000957" "Information collection (understanding supplier behavior)"  "ABM INDUSTRIES INC"            
2020 "000957" "Information collection (understanding supplier behavior)"  "ABM INDUSTRIES INC"            
2020 "000957" "Compliance & onboarding"                                   "ABM INDUSTRIES INC"            
2021 "000957" "Compliance & onboarding"                                   "ABM INDUSTRIES INC"            
2022 "000957" "Information collection (understanding supplier behavior)"  "ABM INDUSTRIES INC"            
2023 "000957" "Information collection (understanding supplier behavior)"  "ABM INDUSTRIES INC"            
2024 "000957" "Compliance & onboarding"                                   "ABM INDUSTRIES INC"            
2025 "000957" "Compliance & onboarding"                                   "ABM INDUSTRIES INC"            
2026 "000957" "Compliance & onboarding"                                   "ABM INDUSTRIES INC"            
2027 "000957" "Compliance & onboarding"                                   "ABM INDUSTRIES INC"            
2018 "001055" "Compliance & onboarding"                                   "AFLAC Incorporated"            
2019 "001055" "Compliance & onboarding"                                   "AFLAC Incorporated"            
2020 "001055" "Information collection (understanding supplier behavior)"  "AFLAC Incorporated"            
2020 "001055" "Compliance & onboarding"                                   "AFLAC Incorporated"            
2021 "001055" "Information collection (understanding supplier behavior)"  "AFLAC Incorporated"            
2022 "001055" "Compliance & onboarding"                                   "AFLAC Incorporated"            
2023 "001055" "Compliance & onboarding"                                   "AFLAC Incorporated"            
2024 "001055" "Compliance & onboarding"                                   "AFLAC Incorporated"            
2025 "001055" "Information collection (understanding supplier behavior)"  "AFLAC Incorporated"            
2026 "001055" "Information collection (understanding supplier behavior)"  "AFLAC Incorporated"            
2027 "001055" "Information collection (understanding supplier behavior)"  "AFLAC Incorporated"            
2018 "00130H" "Information collection (understanding supplier behavior)"  "The AES Corporation"           
2019 "00130H" "Information collection (understanding supplier behavior)"  "The AES Corporation"           
2020 "00130H" "Information collection (understanding supplier behavior)"  "The AES Corporation"           
2021 "00130H" "Information collection (understanding supplier behavior)"  "The AES Corporation"           
2022 "00130H" "Information collection (understanding supplier behavior)"  "The AES Corporation"           
2018 "001547" "Compliance & onboarding"                                   "AK Steel Holding Corporation"  
2019 "001547" "Compliance & onboarding"                                   "AK Steel Holding Corporation"  
2020 "001547" "Compliance & onboarding"                                   "AK Steel Holding Corporation"  
2021 "001547" "Compliance & onboarding"                                   "AK Steel Holding Corporation"  
2022 "001547" "Compliance & onboarding"                                   "AK Steel Holding Corporation"  
2018 "00206R" "Engagement & incentivization (changing supplier behavior)" "AT&T Inc."                     
2018 "00206R" "Information collection (understanding supplier behavior)"  "AT&T Inc."                     
2018 "00206R" "Compliance & onboarding"                                   "AT&T Inc."                     
2019 "00206R" "Information collection (understanding supplier behavior)"  "AT&T Inc."                     
2019 "00206R" "Engagement & incentivization (changing supplier behavior)" "AT&T Inc."                     
2019 "00206R" "Compliance & onboarding"                                   "AT&T Inc."                     
2020 "00206R" "Engagement & incentivization (changing supplier behavior)" "AT&T Inc."                     
2020 "00206R" "Information collection (understanding supplier behavior)"  "AT&T Inc."                     
2020 "00206R" "Compliance & onboarding"                                   "AT&T Inc."                     
2021 "00206R" "Engagement & incentivization (changing supplier behavior)" "AT&T Inc."                     
2022 "00206R" "Engagement & incentivization (changing supplier behavior)" "AT&T Inc."                     
2023 "00206R" "Information collection (understanding supplier behavior)"  "AT&T Inc."                     
2024 "00206R" "Compliance & onboarding"                                   "AT&T Inc."                     
2025 "00206R" "Compliance & onboarding"                                   "AT&T Inc."                     
2026 "00206R" "Compliance & onboarding"                                   "AT&T Inc."                     
2027 "00206R" "Compliance & onboarding"                                   "AT&T Inc."                     
2028 "00206R" "Information collection (understanding supplier behavior)"  "AT&T Inc."                     
2029 "00206R" "Compliance & onboarding"                                   "AT&T Inc."                     
2030 "00206R" "Information collection (understanding supplier behavior)"  "AT&T Inc."                     
2031 "00206R" "Information collection (understanding supplier behavior)"  "AT&T Inc."                     
2032 "00206R" "Engagement & incentivization (changing supplier behavior)" "AT&T Inc."                     
2018 "002824" "Information collection (understanding supplier behavior)"  "Abbott Laboratories"           
2018 "002824" "Innovation & collaboration (changing markets)"             "Abbott Laboratories"           
2018 "002824" "Compliance & onboarding"                                   "Abbott Laboratories"           
2019 "002824" "Information collection (understanding supplier behavior)"  "Abbott Laboratories"           
2019 "002824" "Innovation & collaboration (changing markets)"             "Abbott Laboratories"           
2019 "002824" "Compliance & onboarding"                                   "Abbott Laboratories"           
2020 "002824" "Innovation & collaboration (changing markets)"             "Abbott Laboratories"           
2020 "002824" "Compliance & onboarding"                                   "Abbott Laboratories"           
2020 "002824" "Information collection (understanding supplier behavior)"  "Abbott Laboratories"           
2021 "002824" "Compliance & onboarding"                                   "Abbott Laboratories"           
2022 "002824" "Compliance & onboarding"                                   "Abbott Laboratories"           
2023 "002824" "Compliance & onboarding"                                   "Abbott Laboratories"           
2024 "002824" "Information collection (understanding supplier behavior)"  "Abbott Laboratories"           
2025 "002824" "Compliance & onboarding"                                   "Abbott Laboratories"           
2026 "002824" "Innovation & collaboration (changing markets)"             "Abbott Laboratories"           
2027 "002824" "Information collection (understanding supplier behavior)"  "Abbott Laboratories"           
2028 "002824" "Information collection (understanding supplier behavior)"  "Abbott Laboratories"           
end

                Comment

                • #9
                  Your adaptation of the code was close to what is needed:
                  Code:
                  * Example generated by -dataex-. For more info, type help dataex
clear
input str6 CUSIP str95 CompanyName float Year str220 Type_ENG
"000886" "Royal Caribbean Cruises Ltd"    2018 "Compliance & onboarding"                                  
"000886" "Royal Caribbean Cruises Ltd"    2018 "Information collection (understanding supplier behavior)"
"000886" "Royal Caribbean Cruises Ltd"    2019 "Information collection (understanding supplier behavior)"
"000886" "Royal Caribbean Cruises Ltd"    2019 "Compliance & onboarding"                                  
"000886" "Royal Caribbean Cruises Ltd"    2020 "Compliance & onboarding"                                  
"000886" ""                               2021 ""                                                         
"000886" ""                               2022 ""                                                         
"000943" "LyondellBasell Industries N.V." 2018 "Innovation & collaboration (changing markets)"            
"000943" "LyondellBasell Industries N.V." 2019 "Innovation & collaboration (changing markets)"            
"000943" "LyondellBasell Industries N.V." 2020 "Compliance & onboarding"                                  
"000943" "LyondellBasell Industries N.V." 2020 "Innovation & collaboration (changing markets)"            
"000943" "LyondellBasell Industries N.V." 2020 "Information collection (understanding supplier behavior)"
"000943" ""                               2021 ""                                                         
"000943" ""                               2022 ""                                                         
"000957" "ABM INDUSTRIES INC"             2018 "Compliance & onboarding"                                  
"000957" "ABM INDUSTRIES INC"             2019 "Compliance & onboarding"                                  
"000957" "ABM INDUSTRIES INC"             2019 "Information collection (understanding supplier behavior)"
"000957" "ABM INDUSTRIES INC"             2020 "Information collection (understanding supplier behavior)"
"000957" "ABM INDUSTRIES INC"             2020 "Compliance & onboarding"                                  
"000957" ""                               2021 ""                                                         
"000957" ""                               2022 ""                                                         
"001055" "AFLAC Incorporated"             2018 "Compliance & onboarding"                                  
"001055" "AFLAC Incorporated"             2019 "Compliance & onboarding"                                  
"001055" "AFLAC Incorporated"             2020 "Information collection (understanding supplier behavior)"
"001055" "AFLAC Incorporated"             2020 "Compliance & onboarding"                                  
"001055" ""                               2021 ""                                                         
"001055" ""                               2022 ""                                                         
"00130H" "The AES Corporation"            2018 "Information collection (understanding supplier behavior)"
"00130H" "The AES Corporation"            2019 "Information collection (understanding supplier behavior)"
"00130H" "The AES Corporation"            2020 "Information collection (understanding supplier behavior)"
"00130H" ""                               2021 ""                                                         
"00130H" ""                               2022 ""                                                         
"001547" "AK Steel Holding Corporation"   2018 "Compliance & onboarding"                                  
"001547" "AK Steel Holding Corporation"   2019 "Compliance & onboarding"                                  
"001547" ""                               2020 ""                                                         
"001547" ""                               2021 ""                                                         
"001547" ""                               2022 ""                                                         
"00206R" "AT&T Inc."                      2018 "Compliance & onboarding"                                  
"00206R" "AT&T Inc."                      2018 "Engagement & incentivization (changing supplier behavior)"
"00206R" "AT&T Inc."                      2018 "Information collection (understanding supplier behavior)"
"00206R" "AT&T Inc."                      2019 "Compliance & onboarding"                                  
"00206R" "AT&T Inc."                      2019 "Information collection (understanding supplier behavior)"
"00206R" "AT&T Inc."                      2019 "Engagement & incentivization (changing supplier behavior)"
"00206R" "AT&T Inc."                      2020 "Information collection (understanding supplier behavior)"
"00206R" "AT&T Inc."                      2020 "Compliance & onboarding"                                  
"00206R" "AT&T Inc."                      2020 "Engagement & incentivization (changing supplier behavior)"
"00206R" ""                               2021 ""                                                         
"00206R" ""                               2022 ""                                                         
"002824" "Abbott Laboratories"            2018 "Compliance & onboarding"                                  
"002824" "Abbott Laboratories"            2018 "Innovation & collaboration (changing markets)"            
"002824" "Abbott Laboratories"            2018 "Information collection (understanding supplier behavior)"
"002824" "Abbott Laboratories"            2019 "Compliance & onboarding"                                  
"002824" "Abbott Laboratories"            2019 "Innovation & collaboration (changing markets)"            
"002824" "Abbott Laboratories"            2019 "Information collection (understanding supplier behavior)"
"002824" "Abbott Laboratories"            2020 "Information collection (understanding supplier behavior)"
"002824" "Abbott Laboratories"            2020 "Innovation & collaboration (changing markets)"            
"002824" "Abbott Laboratories"            2020 "Compliance & onboarding"                                  
"002824" ""                               2021 ""                                                         
"002824" ""                               2022 ""                                                         
"00287Y" "AbbVie Inc"                     2018 "Information collection (understanding supplier behavior)"
"00287Y" "AbbVie Inc"                     2019 "Information collection (understanding supplier behavior)"
"00287Y" "AbbVie Inc"                     2020 "Information collection (understanding supplier behavior)"
"00287Y" ""                               2021 ""                                                         
"00287Y" ""                               2022 ""                                                         
"002896" "Abercrombie & Fitch Co."        2018 "Information collection (understanding supplier behavior)"
"002896" ""                               2019 ""                                                         
"002896" ""                               2020 ""                                                         
"002896" ""                               2021 ""                                                         
"002896" ""                               2022 ""                                                         
"004432" "Chubb Limited"                  2018 "Information collection (understanding supplier behavior)"
"004432" "Chubb Limited"                  2019 "Information collection (understanding supplier behavior)"
"004432" "Chubb Limited"                  2020 "Information collection (understanding supplier behavior)"
"004432" ""                               2021 ""                                                         
"004432" ""                               2022 ""                                                         
"00508Y" ""                               2018 ""                                                         
"00508Y" "Acuity Brands Inc"              2019 "Information collection (understanding supplier behavior)"
"00508Y" ""                               2020 ""                                                         
"00508Y" ""                               2021 ""                                                         
"00508Y" ""                               2022 ""                                                         
"00724F" "Adobe"                          2018 "Innovation & collaboration (changing markets)"            
"00724F" "Adobe"                          2019 "Innovation & collaboration (changing markets)"            
"00724F" "Adobe"                          2020 "Information collection (understanding supplier behavior)"
"00724F" ""                               2021 ""                                                         
"00724F" ""                               2022 ""                                                         
"00738A" "ADTRAN INC"                     2018 "Engagement & incentivization (changing supplier behavior)"
"00738A" "ADTRAN INC"                     2019 "Information collection (understanding supplier behavior)"
"00738A" "ADTRAN INC"                     2020 "Information collection (understanding supplier behavior)"
"00738A" ""                               2021 ""                                                         
"00738A" ""                               2022 ""                                                         
"00751Y" ""                               2018 ""                                                         
"00751Y" "Advance Auto Parts Inc"         2019 "Information collection (understanding supplier behavior)"
"00751Y" "Advance Auto Parts Inc"         2020 "Information collection (understanding supplier behavior)"
"00751Y" ""                               2021 ""                                                         
"00751Y" ""                               2022 ""                                                         
"007903" "Advanced Micro Devices, Inc"    2018 "Compliance & onboarding"                                  
"007903" "Advanced Micro Devices, Inc"    2019 "Information collection (understanding supplier behavior)"
"007903" "Advanced Micro Devices, Inc"    2020 "Information collection (understanding supplier behavior)"
"007903" ""                               2021 ""                                                         
"007903" ""                               2022 ""                                                         
"00846U" "Agilent Technologies Inc."      2018 "Engagement & incentivization (changing supplier behavior)"
end


preserve
keep CUSIP Type_ENG CompanyName
duplicates drop
expand `=2022-2018+1'
by CUSIP Type_ENG CompanyName, sort: gen Year = 2018 + _n - 1
isid CUSIP Type_ENG CompanyName Year, sort missok
tempfile holding
save `holding'

restore
merge m:1 CUSIP Type_ENG CompanyName Year using `holding', assert(match using) nogenerate
sort CUSIP Year

                  Comment

                  Previous Next
                  Working...
                  X