Announcement

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

    Reducing Wage Data by Historical Minimum Wage

    I have calculated wage adjusted for CPI (Consumer Price Index) across 15 years, each year divided by 4 quarters, except in the final year (2022) where there is only 3 quarters of data available. Although I have restricted the data to full-time workers, working at least 35 hours, the data is showing earnings below the minimum wage at age 18 and above (likely accounting for volunteers, self-employed workers etc.). I would like to restrict the data to minimum wage for each respective year and quarter, but am unclear how to achieve this. I have captured some of my data below for visualisation (listing 100 out of 90500211 observations):

    Code:
    * Example generated by -dataex-. For more info, type help dataex
clear
input float(m_yr m_yrqtr2 m_paygro_cpi)
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071  9.122694
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071 1065.5308
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071 1044.5485
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071 18245.389
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071  6385.886
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071  3206.627
2007 20071         .
2007 20071  91222.38
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071 301.04892
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071  43788.93
2007 20071  28320.49
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071  466.1697
2007 20071  694.2371
2007 20071         .
2007 20071         .
2007 20071  91225.12
2007 20071  49262.55
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071 565.60706
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071         .
end
label values m_yr m_yrla

    Here is a snapshot of my excel worksheet with the minimum wage data:
    Code:
     
    2007
Quarter 1-3
   8,099.00

    

    
Quarter 4
   8,372.00

    

    2008
Quarter 1-3
   8,372.00

    

    
Quarter 4
   8,554.00

    

    2009
Quarter 1-3
   8,554.00

    

    
Quarter 4
   8,790.60

    

    2010
Quarter 1-3
   8,790.60

    

    
Quarter 4
   8,954.40

    

    2011
Quarter 1-3
   8,954.40

    

    
Quarter 4
   9,063.60

    

    2012
Quarter 1-3
   9,063.60

    

    
Quarter 4
   9,063.60

    

    2013
Quarter 1-3
   9,063.60

    

    
Quarter 4
   9,154.60

    

    2014
Quarter 1-3
   9,154.60

    

    
Quarter 4
   9,336.60

    

    2015
Quarter 1-3
   9,336.60

    

    
Quarter 4
   9,646.00

    

    2016
Quarter 1
   9,646.00

    

    
Quarter 2-3
   9,646.00

    

    
Quarter 4
 10,101.00

    

    2017
Quarter 1
 10,101.00

    

    
Quarter 2-4
 10,192.00

    

    2018
Quarter 1
 10,192.00

    

    
Quarter 2-4
 10,738.00

    

    2019
Quarter 1
 10,738.00

    

    
Quarter 2-4
 11,193.00

    

    2020
Quarter 1
 11,193.00

    

    
Quarter 2-4
 11,739.00

    

    2021
Quarter 1
 11,739.00

    

    
Quarter 2-4
 11,939.20

    

    2022
Quarter 1
 11,939.20

    

    
Quarter 2-3
 12,430.60
    Thanks in advance!
    Tags: None
  • #2
    I would like to restrict the data to minimum wage for each respective year and quarter, but am unclear how to achieve this.
    What does restrict mean here? Delete observations that have values below the minimum wage? If so, merge the minimum wage dataset with the current dataset on year and quarter and then:

    Code:
    drop if m_paygro_cpi< minimum_wage
    If you are facing problems with merging the datasets, provide an example of the minimum wage dataset using dataex. That is, after importing the Excel file to Stata.
    • 1 like

    Comment

    • #3
      Thanks Andrew, that is exactly what I mean by restricting here. The question does then become how to merge the two datasets. Here is an example of the minimum wage dataset once imported into Stata:

      Code:
      input str6 yrqtr double min_wage
"2007q1"               8099
"2007q2"               8099
"2007q3"               8099
"2007q4"               8372
"2008q1"               8372
"2008q2"               8372
"2008q3"               8372
"2008q4"               8554
"2009q1"               8554
"2009q2"               8554
"2009q3"             8790.6
"2009q4"             8790.6
"2010q1"             8790.6
"2010q2"             8790.6
"2010q3"             8790.6
"2010q4"             8954.4
"2011q1"             8954.4
"2011q2"             8954.4
"2011q3"             8954.4
"2011q4"             9063.6
"2012q1"             9063.6
"2012q2"             9063.6
"2012q3"             9063.6
"2012q4"             9063.6
"2013q1"             9063.6
"2013q2"             9063.6
"2013q3"             9063.6
"2013q4"             9154.6
"2014q1"             9154.6
"2014q2"             9154.6
"2014q3"             9154.6
"2014q4"  9336.599999999999
"2015q1"  9336.599999999999
"2015q2"  9336.599999999999
"2015q3"  9336.599999999999
"2015q4"               9646
"2016q1"               9646
"2016q2"               9646
"2016q3"               9646
"2016q4"              10101
"2017q1"              10101
"2017q2"              10192
"2017q3"              10192
"2017q4"              10192
"2018q1"              10192
"2018q2"              10738
"2018q3"              10738
"2018q4"              10738
"2019q1"              10738
"2019q2"              11193
"2019q3"              11193
"2019q4"              11193
"2020q1"              11193
"2020q2"              11739
"2020q3"              11739
"2020q4"              11739
"2021q1"              11739
"2021q2" 11939.199999999999
"2021q3" 11939.199999999999
"2021q4" 11939.199999999999
"2022q1" 11939.199999999999
"2022q2"            12430.6
"2022q3"            12430.6
"2022q4"            12430.6
end
      I can also import without the q if it helps with coding (e.g., 20071 rather than 2007q1).

      Comment

      • #4
        Thanks for the data example. I assume that missing values for "m_paygro_cpi" are not useful and can be deleted. If not, remove the part of the code highlighted in red.

        Code:
        input str6 yrqtr double min_wage
"2007q1"               8099
"2007q2"               8099
"2007q3"               8099
"2007q4"               8372
"2008q1"               8372
"2008q2"               8372
"2008q3"               8372
"2008q4"               8554
"2009q1"               8554
"2009q2"               8554
"2009q3"             8790.6
"2009q4"             8790.6
"2010q1"             8790.6
"2010q2"             8790.6
"2010q3"             8790.6
"2010q4"             8954.4
"2011q1"             8954.4
"2011q2"             8954.4
"2011q3"             8954.4
"2011q4"             9063.6
"2012q1"             9063.6
"2012q2"             9063.6
"2012q3"             9063.6
"2012q4"             9063.6
"2013q1"             9063.6
"2013q2"             9063.6
"2013q3"             9063.6
"2013q4"             9154.6
"2014q1"             9154.6
"2014q2"             9154.6
"2014q3"             9154.6
"2014q4"  9336.599999999999
"2015q1"  9336.599999999999
"2015q2"  9336.599999999999
"2015q3"  9336.599999999999
"2015q4"               9646
"2016q1"               9646
"2016q2"               9646
"2016q3"               9646
"2016q4"              10101
"2017q1"              10101
"2017q2"              10192
"2017q3"              10192
"2017q4"              10192
"2018q1"              10192
"2018q2"              10738
"2018q3"              10738
"2018q4"              10738
"2019q1"              10738
"2019q2"              11193
"2019q3"              11193
"2019q4"              11193
"2020q1"              11193
"2020q2"              11739
"2020q3"              11739
"2020q4"              11739
"2021q1"              11739
"2021q2" 11939.199999999999
"2021q3" 11939.199999999999
"2021q4" 11939.199999999999
"2022q1" 11939.199999999999
"2022q2"            12430.6
"2022q3"            12430.6
"2022q4"            12430.6
end

gen qdate= quarterly(yrqtr, "yq")
drop yrqtr
tempfile qdate
save `qdate'

* Example generated by -dataex-. For more info, type help dataex
clear
input float(m_yr m_yrqtr2 m_paygro_cpi)
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071  9.122694
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071 1065.5308
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071 1044.5485
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071 18245.389
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071  6385.886
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071  3206.627
2007 20071         .
2007 20071  91222.38
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071 301.04892
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071  43788.93
2007 20071  28320.49
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071  466.1697
2007 20071  694.2371
2007 20071         .
2007 20071         .
2007 20071  91225.12
2007 20071  49262.55
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071 565.60706
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071         .
2007 20071         .
end
label values m_yr m_yrla

gen yrqtr= substr(string(m_yrqtr2), 1, 4)+ "q"+ substr(string(m_yrqtr2), 5, .)
gen qdate= quarterly(yrqtr, "yq")
merge m:1 qdate using `qdate', keep(master match) nogen
format qdate %tq
drop m_yr m_yrqtr2 yrqtr
order qdate m_paygro_cpi min_wage
drop if m_paygro_cpi<min_wage | missing(m_paygro_cpi)
        Res.:

        Code:
        . l, sepby(qdate)

     +------------------------------+
     |  qdate   m_payg~i   min_wage |
     |------------------------------|
  1. | 2007q1   18245.39       8099 |
  2. | 2007q1   91222.38       8099 |
  3. | 2007q1   43788.93       8099 |
  4. | 2007q1   28320.49       8099 |
  5. | 2007q1   91225.12       8099 |
  6. | 2007q1   49262.55       8099 |
     +------------------------------+
        Last edited by Andrew Musau; 03 Feb 2023, 09:58.

        Comment

        • #5
          Hi Andrew, thanks for this, I have been trying to get it to work with this code, but its not happening. It won't merge because the master data (m_yrqtr2) is float and the imported data (m_yrqtr2) is string. Dropping the 2 from the variable name (and ignoring the 'q' in 2007q1 etc.) I used the code:

          Code:
          destring m_yrqtr, replace float force ignore("q")
          But it changed the data to byte rather than float, so I tried

          Code:
          destring m_yrqtr, replace float force ignore("q")
recast float m_yrqtr, force
          And now the data is missing!?!?

          Comment

          • #6
            Sorry, I missed your reply. Look at the full code. I create two date variables. First

            gen qdate= quarterly(yrqtr, "yq")
            drop yrqtr
            tempfile qdate
            save `qdate'
            then

            gen yrqtr= substr(string(m_yrqtr2), 1, 4)+ "q"+ substr(string(m_yrqtr2), 5, .)
            gen qdate= quarterly(yrqtr, "yq")
            merge m:1 qdate using `qdate', keep(master match) nogen
            format qdate %tq
            drop m_yr m_yrqtr2 yrqtr
            order qdate m_paygro_cpi min_wage
            drop if m_paygro_cpi<min_wage | missing(m_paygro_cpi)
            Did you miss one of the steps? Do not destring anything, do as I do.

            Comment

            • #7
              Not to worry at all, I'm just glad to get the help!

              I have taken the first step without issue, but when I get to the second step

              Code:
              gen yrqtr= substr(string(m_yrqtr2), 1, 4)+ "q"+ substr(string(m_yrqtr2), 5, .)

              I get the following error:

              type mismatch

              Perhaps I have not adequately provided you a snapshot of the data in the two files. The first file imported from Excel looks like this:

              Code:
              input str6 yrqtr double min_wage
"2007q1"               8099
"2007q2"               8099
"2007q3"               8099
"2007q4"               8372
"2008q1"               8372
"2008q2"               8372
"2008q3"               8372
"2008q4"               8554
"2009q1"               8554
"2009q2"               8554
"2009q3"             8790.6
"2009q4"             8790.6
"2010q1"             8790.6
"2010q2"             8790.6
"2010q3"             8790.6
"2010q4"             8954.4
"2011q1"             8954.4
"2011q2"             8954.4
"2011q3"             8954.4
"2011q4"             9063.6
"2012q1"             9063.6
"2012q2"             9063.6
"2012q3"             9063.6
"2012q4"             9063.6
"2013q1"             9063.6
"2013q2"             9063.6
"2013q3"             9063.6
"2013q4"             9154.6
"2014q1"             9154.6
"2014q2"             9154.6
"2014q3"             9154.6
"2014q4"  9336.599999999999
"2015q1"  9336.599999999999
"2015q2"  9336.599999999999
"2015q3"  9336.599999999999
"2015q4"               9646
"2016q1"               9646
"2016q2"               9646
"2016q3"               9646
"2016q4"              10101
"2017q1"              10101
"2017q2"              10192
"2017q3"              10192
"2017q4"              10192
"2018q1"              10192
"2018q2"              10738
"2018q3"              10738
"2018q4"              10738
"2019q1"              10738
"2019q2"              11193
"2019q3"              11193
"2019q4"              11193
"2020q1"              11193
"2020q2"              11739
"2020q3"              11739
"2020q4"              11739
"2021q1"              11739
"2021q2" 11939.199999999999
"2021q3" 11939.199999999999
"2021q4" 11939.199999999999
"2022q1" 11939.199999999999
"2022q2"            12430.6
"2022q3"            12430.6
"2022q4"            12430.6

              The second, main file looks like this:

              Code:
              input float(m_yrqtr m_yrqtr2 m_paygro_cpi)
168 20021     .
168 20021     .
168 20021     .
168 20021     .
168 20021 18500
168 20021 12500
168 20021     .
168 20021     .
168 20021     .
168 20021  1800
168 20021     .
168 20021     .
168 20021     .
168 20021     .
168 20021     .
168 20021     .
168 20021  7000
168 20021     .
168 20021     .
168 20021     .
168 20021     .
168 20021     .
168 20021     .
168 20021     .
168 20021     .
168 20021   347
168 20021     .
168 20021     .
168 20021     .
168 20021 21500
168 20021     .
168 20021     .
168 20021     .
168 20021     .
168 20021     .
168 20021     .
168 20021     .
168 20021     .
168 20021     .
168 20021     .
168 20021     .
168 20021     .
168 20021     .
168 20021 37000
168 20021     .
168 20021     .
168 20021     .
168 20021  1091
168 20021     .
168 20021     .
168 20021     .
168 20021     .
168 20021     .
168 20021     .
168 20021     .
168 20021 10500
168 20021     .
168 20021   811
168 20021   123
168 20021     .
168 20021     .
168 20021     .
168 20021     .
168 20021     .
168 20021     .
168 20021     .
168 20021     .
168 20021     .
168 20021     .
168 20021     .
168 20021   220
168 20021     .
168 20021     .
168 20021     .
168 20021     .
168 20021     .
168 20021     .
168 20021     .
168 20021     .
168 20021     .
168 20021     .
168 20021   500
168 20021     .
168 20021     .
168 20021     .
168 20021     .
168 20021 30000
168 20021     .
168 20021     .
168 20021     .
168 20021     .
168 20021 15000
168 20021     .
168 20021     .
168 20021     .
168 20021     .
168 20021     .
168 20021     .
168 20021     .
168 20021     .
end
format %tq m_yrqtr
label values m_paygro_cpi m_paygro_cpila

              Please note: "m_yrqtr2" in the main file, runs from quarter 1 2002 (20021) through to quarter 3 2022 (20223). But the minimum wage "yrqtr" data only runs from quarter 1 2007 (2007q1) to quarter 3 2022 (2022q3).

              Sorry, if this has confused things, and is the reason that the code does not work

              Comment

              • #8
                A type mismatch error results from mixing up a string and a numerical variable. That is why it is important to use dataex to present a reproducible example so that I see your variables exactly as they are. As far as there being some duration where the minimum wage data is not observed, that does not matter. Running the code in #4 with the modification that minimum wage can be missing still produces the desired results.

                Code:
                clear
input str6 yrqtr double min_wage
"2007q1"               8099
"2007q2"               8099
"2007q3"               8099
"2007q4"               8372
"2008q1"               8372
"2008q2"               8372
"2008q3"               8372
"2008q4"               8554
"2009q1"               8554
"2009q2"               8554
"2009q3"             8790.6
"2009q4"             8790.6
"2010q1"             8790.6
"2010q2"             8790.6
"2010q3"             8790.6
"2010q4"             8954.4
"2011q1"             8954.4
"2011q2"             8954.4
"2011q3"             8954.4
"2011q4"             9063.6
"2012q1"             9063.6
"2012q2"             9063.6
"2012q3"             9063.6
"2012q4"             9063.6
"2013q1"             9063.6
"2013q2"             9063.6
"2013q3"             9063.6
"2013q4"             9154.6
"2014q1"             9154.6
"2014q2"             9154.6
"2014q3"             9154.6
"2014q4"  9336.599999999999
"2015q1"  9336.599999999999
"2015q2"  9336.599999999999
"2015q3"  9336.599999999999
"2015q4"               9646
"2016q1"               9646
"2016q2"               9646
"2016q3"               9646
"2016q4"              10101
"2017q1"              10101
"2017q2"              10192
"2017q3"              10192
"2017q4"              10192
"2018q1"              10192
"2018q2"              10738
"2018q3"              10738
"2018q4"              10738
"2019q1"              10738
"2019q2"              11193
"2019q3"              11193
"2019q4"              11193
"2020q1"              11193
"2020q2"              11739
"2020q3"              11739
"2020q4"              11739
"2021q1"              11739
"2021q2" 11939.199999999999
"2021q3" 11939.199999999999
"2021q4" 11939.199999999999
"2022q1" 11939.199999999999
"2022q2"            12430.6
"2022q3"            12430.6
"2022q4"            12430.6
end

gen qdate= quarterly(yrqtr, "yq")
drop yrqtr
tempfile qdate
save `qdate'
clear

input float(m_yrqtr m_yrqtr2 m_paygro_cpi)
168 20021     .
168 20021     .
168 20021     .
168 20021     .
168 20021 18500
168 20021 12500
168 20021     .
168 20021     .
168 20021     .
168 20021  1800
168 20021     .
168 20021     .
168 20021     .
168 20021     .
168 20021     .
168 20021     .
168 20021  7000
168 20021     .
168 20021     .
168 20021     .
168 20021     .
168 20021     .
168 20021     .
168 20021     .
168 20021     .
168 20021   347
168 20021     .
168 20021     .
168 20021     .
168 20021 21500
168 20021     .
168 20021     .
168 20021     .
168 20021     .
168 20021     .
168 20021     .
168 20021     .
168 20021     .
168 20021     .
168 20021     .
168 20021     .
168 20021     .
168 20021     .
168 20021 37000
168 20021     .
168 20021     .
168 20021     .
168 20021  1091
168 20021     .
168 20021     .
168 20021     .
168 20021     .
168 20021     .
168 20021     .
168 20021     .
168 20021 10500
168 20021     .
168 20021   811
168 20021   123
168 20021     .
168 20021     .
168 20021     .
168 20021     .
168 20021     .
168 20021     .
168 20021     .
168 20021     .
168 20021     .
168 20021     .
168 20021     .
168 20021   220
168 20021     .
168 20021     .
168 20021     .
168 20021     .
168 20021     .
168 20021     .
168 20021     .
168 20021     .
168 20021     .
168 20021     .
168 20021   500
168 20021     .
168 20021     .
168 20021     .
168 20021     .
168 20021 30000
168 20021     .
168 20021     .
168 20021     .
168 20021     .
168 20021 15000
168 20021     .
168 20021     .
168 20021     .
168 20021     .
168 20021     .
168 20021     .
168 20021     .
168 20021     .
end
format %tq m_yrqtr
label values m_paygro_cpi m_paygro_cpila


gen yrqtr= substr(string(m_yrqtr2), 1, 4)+ "q"+ substr(string(m_yrqtr2), 5, .)
gen qdate= quarterly(yrqtr, "yq")
merge m:1 qdate using `qdate', keep(master match) nogen
format qdate %tq
drop m_yrqtr m_yrqtr2 yrqtr
order qdate m_paygro_cpi min_wage
drop if m_paygro_cpi<min_wage & !missing(min_wage) | missing(m_paygro_cpi)
                Here with no match for the minimum wage, we get

                Res.:

                Code:
                . l, sep(0)

     +------------------------------+
     |  qdate   m_payg~i   min_wage |
     |------------------------------|
  1. | 2002q1      18500          . |
  2. | 2002q1      12500          . |
  3. | 2002q1       1800          . |
  4. | 2002q1       7000          . |
  5. | 2002q1        347          . |
  6. | 2002q1      21500          . |
  7. | 2002q1      37000          . |
  8. | 2002q1       1091          . |
  9. | 2002q1      10500          . |
 10. | 2002q1        811          . |
 11. | 2002q1        123          . |
 12. | 2002q1        220          . |
 13. | 2002q1        500          . |
 14. | 2002q1      30000          . |
 15. | 2002q1      15000          . |
     +------------------------------+

                Comment

                • #9
                  Hi there, I tried:

                  Code:
                  use "/Users/-Quiet Quitting/Minimum Wage Data.dta", clear

gen qdate= quarterly(yrqtr, "yq")
drop yrqtr
tempfile qdate
save `qdate'
clear
                  Then:

                  Code:
                  use "/Users/-Quiet Quitting/DATA/Main.dta", clear

gen yrqtr= substr(string(m_yrqtr2), 1, 4)+ "q"+ substr(string(m_yrqtr2), 5, .)
gen qdate= quarterly(yrqtr, "yq")
merge m:1 qdate using `qdate' , keep(master match) nogen
format qdate %tq
drop m_yrqtr m_yrqtr2 yrqtr
order qdate m_paygro_cpi min_wage
drop if m_paygro_cpi&lt;min_wage &amp; !missing(min_wage) | missing(m_paygro_cpi)

                  Sadly, I am still getting the error:

                  invalid file specification
                  After code:

                  Code:
                   merge m:1 qdate using `qdate', keep(master match) nogen

                  I also tried:

                  Code:
                  use "/Users/-Quiet Quitting/DATA/Main.dta", clear

gen qdate= quarterly(yrqtr, "yq")
drop yrqtr
tempfile qdate
save `qdate'
clear

gen yrqtr= substr(string(m_yrqtr2), 1, 4)+ "q"+ substr(string(m_yrqtr2), 5, .)
gen qdate= quarterly(yrqtr, "yq")
merge m:1 qdate using `qdate' , keep(master match) nogen
format qdate %tq
drop m_yrqtr m_yrqtr2 yrqtr
order qdate m_paygro_cpi min_wage
drop if m_paygro_cpi&lt;min_wage &amp; !missing(min_wage) | missing(m_paygro_cpi)
                  But got the error:

                  yrqtr not found
                  After code:

                  Code:
                  gen qdate= quarterly(yrqtr, "yq")

                  Not sure where to go from here? Sorry.

                  Comment

                  • #10
                    Try

                    Code:
                    use "/Users/-Quiet Quitting/Minimum Wage Data.dta", clear
gen qdate= quarterly(yrqtr, "yq")
drop yrqtr
save "/Users/-Quiet Quitting/qdate.dta", replace
clear
use "/Users/-Quiet Quitting/DATA/Main.dta", clear
gen yrqtr= substr(string(m_yrqtr2), 1, 4)+ "q"+ substr(string(m_yrqtr2), 5, .)
gen qdate= quarterly(yrqtr, "yq")
merge m:1 qdate using "/Users/-Quiet Quitting/qdate.dta" , keep(master match) nogen
format qdate %tq
drop m_yrqtr m_yrqtr2 yrqtr
order qdate m_paygro_cpi min_wage
drop if m_paygro_cpi<min_wage & !missing(min_wage) | missing(m_paygro_cpi)
                    where you specify the full path names.
                    Last edited by Andrew Musau; 11 Feb 2023, 06:46.

                    Comment

                    • #11
                      It appears we have made progress! The merge was successful, although the dropping of variables is leading to nil observations. I suspect the problem is that the minimum wage data is not in the right format (wide, rather than long)???

                      See the outputs below:

                      Code:
                       {
.         preserve
.
.         sum m_paygro_cpi min_wage

    Variable |        Obs        Mean    Std. dev.       Min        Max
-------------+---------------------------------------------------------
m_paygro_cpi |    980,376     17409.8    23855.41          1      99995
    min_wage |         64    9827.716    1275.775       8099    12430.6
.
.         drop if m_paygro_cpi &lt; min_wage &amp; !missing(min_wage) | missing(m_pay
&gt; gro_cpi)
(89,519,899 observations deleted)
.
.         sum m_paygro_cpi min_wage

    Variable |        Obs        Mean    Std. dev.       Min        Max
-------------+---------------------------------------------------------
m_paygro_cpi |    980,376     17409.8    23855.41          1      99995
    min_wage |          0
.        
.         restore
. }
                      Code:
                      {
.         preserve
.
.         sum m_paygro_cpi min_wage

    Variable |        Obs        Mean    Std. dev.       Min        Max
-------------+---------------------------------------------------------
m_paygro_cpi |    980,376     17409.8    23855.41          1      99995
    min_wage |         64    9827.716    1275.775       8099    12430.6
.
.         drop if m_paygro_cpi &lt; min_wage //&amp; !missing(min_wage) | missing(m_p
&gt; aygro_cpi)
(980,376 observations deleted)
.
.         sum m_paygro_cpi min_wage

    Variable |        Obs        Mean    Std. dev.       Min        Max
-------------+---------------------------------------------------------
m_paygro_cpi |          0
    min_wage |         64    9827.716    1275.775       8099    12430.6

restore
. }
                      How to change the minimum wage data from wide to long I am not entirely clear on. I tried the following to no avail:

                      Code:
                      generate seq = _n          
reshape long min_wage, i(seq) j(when2) string
                      Error message:
                      Code:
                      no xij variables found
    You typed something like reshape wide a b, i(i) j(j).
    reshape looked for existing variables named a# and b# but could not find
    any. Remember this picture:

         long                                wide
        +---------------+                   +------------------+
        | i   j   a   b |                   | i   a1 a2  b1 b2 |
        |---------------| &lt;--- reshape ---&gt; |------------------|
        | 1   1   1   2 |                   | 1   1   3   2  4 |
        | 1   2   3   4 |                   | 2   5   7   6  8 |
        | 2   1   5   6 |                   +------------------+
        | 2   2   7   8 |
        +---------------+

        long to wide: reshape wide a b, i(i) j(j)    (j existing variable)
        wide to long: reshape long a b, i(i) j(j)    (j new variable)

                      Comment

                      • #12
                        The merge was successful, although the dropping of variables is leading to nil observations.
                        Present a sample of the merged dataset before the drop command.

                        Comment

                        • #13
                          Thank you ever so much for sticking with me on this AND resolving it Andrew! It works perfectly, and now, as a bonus, my analyses are running so much faster!!

                          Comment

                          Previous Next
                          Working...
                          X