How to loop over quarters starting at third quarter

[Greg Saldutte]

Hello,

I am dealing with fixed width data in a .txt file that includes date. The data spans 1973 to 2014 and is filed quarterly. The date on which the data was filed is included as a variable.

000148862STUCKEY,JAMES E,       19730930AA0011001000150-54 03GS0515-19 0301C008465810F1
000278418PALMER BETTY M          19730930AA0011001000130-34 07GS0710-14 0318C010471810F1
003141713SHAW MARY J                19730930AA0011001000135-39 04GS07UNSP  **** 009520815F1

Here you can see the filing date is in columns 33-40.

I am attemping to read .txt files beginning in the third quarter of 1973 through the last quarter of 2014, i.e., first the third and fourth quarters of 1973, then the first, second, and so on of 1974. I used this code:

forval year = 1973/2014{

    forval mth = 03(3)12{

        infix float id 1-9 str name 10-32 float filedate 33-40 str agency 41-44 ///
         str station 45-53 str age 54-59 str yearssincedegree 60-65 str educationlevel 66-67 ///
         str payplan 68-69 str grade 70-71 str los 72-77 str occupation 78-81 ///
         str occ_cat 82 str pay 83-88 str supervisory_status 89 str appointment 90-91 ///
         str schedule 92 str nsftp 93 using Status_Non_DoD_1973_12.txt, clear
         
        gen date2=date(string(filedate,"%8.0f"),"YMD")
        format date2 %td
        
        replace filedate = date2
        
        drop date2
        
        save Status_Non_DoD_`year'_`mth', replace
    }
}

I thought that this code would skip over the 03 and 06 values for 1973 because those files don't exist. However, it reads in the .txt file ending in 1973_09 and saves it as a file ending in 1973_03. I would like to know how to fix this.

Thank you.

[Clyde Schechter]

No, Stata doesn't skip things when it can't find a file you've asked it for: in a file write operation it creates the file, and in a file-read operation it aborts with an error message (unless you -capture- it.) The reason it didn't abort on a read error in your case is that there is an additional error in your code that you didn't notice. Your -infix- command specifies only a single file, Status_Non_DoD_1973_12.txt, and it repeatedly reads that same file each time through the loop. Apparently that file does exist.

I think the best way to handle this situation is to reduce it to a single loop over a Stata internal format quarterly date, and to extract the month and year from that inside the loop. Like this:

local start = tq(1973q3)
local end = tq(2014q4)

forvalues qdate = `start'/`end' {
       local year = year(dofq(`qdate'))
        local mth = month(dofq(`qdate'))
        infix float id 1-9 str name 10-32 float filedate 33-40 str agency 41-44 ///
         str station 45-53 str age 54-59 str yearssincedegree 60-65 str educationlevel 66-67 ///
         str payplan 68-69 str grade 70-71 str los 72-77 str occupation 78-81 ///
         str occ_cat 82 str pay 83-88 str supervisory_status 89 str appointment 90-91 ///
         str schedule 92 str nsftp 93 using Status_Non_DoD_`year'_`mth'.txt, clear
        
        gen date2=date(string(filedate,"%8.0f"),"YMD")
        format date2 %td
        
        replace filedate = date2
        
        drop date2
        
        save Status_Non_DoD_`year'_`mth', replace
}

[Greg Saldutte]

Thank you for this quick, detailed response. I believe it should work with some tweaks, but right now I get the error: "file Status_Non_DoD_1973_7.txt not found."

Though my data is quarterly, the .txt files are labelled according to month - 1973_09, 1973_12, 1974_03, 1974_06, and so on. Would you have an idea on how to solve this? I have referred to the Stata documentation, and though the %td format seems logical on the surface, I don't think it would work.

[William Lisowski]

Here is some example code based on Clyde's that I think will point you in a helpful direction. The two problems were that you need the leading zero on the month number, and your quarters are labelled with the ending month rather than the starting month.

cls
// set start and end - end early for this example code
local start = tq(1973q3)
local end = tq(1976q4)

forvalues qdate = `start'/`end' {
    local year = year(dofq(`qdate'))
    local mth : display %02.0f month(dofq(`qdate'))+2
    // see if it works
    display "Status_Non_DoD_`year'_`mth'.txt"
}

Status_Non_DoD_1973_09.txt
Status_Non_DoD_1973_12.txt
Status_Non_DoD_1974_03.txt
Status_Non_DoD_1974_06.txt
Status_Non_DoD_1974_09.txt
Status_Non_DoD_1974_12.txt
Status_Non_DoD_1975_03.txt
Status_Non_DoD_1975_06.txt
Status_Non_DoD_1975_09.txt
Status_Non_DoD_1975_12.txt
Status_Non_DoD_1976_03.txt
Status_Non_DoD_1976_06.txt
Status_Non_DoD_1976_09.txt
Status_Non_DoD_1976_12.txt

[Clyde Schechter]

Yes, I think William Lisowski has it right. The problem is that my code was extracting the month in which the quarter begins, whereas your file names have the month in which the quarter ends. His code fixes that.

[Greg Saldutte]

Thank you, William. That code is incredibly helpful. If I may ask one more question: How would one display the month name instead of number, i.e., ...1976_December instead of 1976_12? Thanks.

[Clyde Schechter]

cls
// set start and end - end early for this example code
local start = tq(1973q3)
local end = tq(1976q4)

forvalues qdate = `start'/`end' {
    local year = year(dofq(`qdate'))
    local mth : display %02.0f month(dofq(`qdate'))+2
    local month: word `mth' of `c(Months)'
    // see if it works
    display "Status_Non_DoD_`year'_`month'.txt"
}

[William Lisowski]

As a challenge to myself, and because it was more amusing than what I am supposed to be doing, I started de novo and came up with the following, for which I make no claims beyond brevity and opacity. I would not choose to use these file names because when sorted in alphabetical order they fail to be in chronological order, complicating the task of processing them sequentially.

clear
// set start and end - end early for this example code
local start = tq(1973q3)
local end = tq(1976q4)

forvalues qdate = `start'/`end' {
    local ym = trim("`: display %tmCCYY!_Month mofd(dofq(`qdate'))+2'")
    // see if it works
    quietly save "Status_Non_DoD_`ym'.txt", emptyok replace
}

ls Status*

. ls Status*

-rw-r--r--  1 lisowskiw  staff  555 May 24 15:08 Status_Non_DoD_1973_December.txt
-rw-r--r--  1 lisowskiw  staff  555 May 24 15:08 Status_Non_DoD_1973_September.txt
-rw-r--r--  1 lisowskiw  staff  555 May 24 15:08 Status_Non_DoD_1974_December.txt
-rw-r--r--  1 lisowskiw  staff  555 May 24 15:08 Status_Non_DoD_1974_June.txt
-rw-r--r--  1 lisowskiw  staff  555 May 24 15:08 Status_Non_DoD_1974_March.txt
-rw-r--r--  1 lisowskiw  staff  555 May 24 15:08 Status_Non_DoD_1974_September.txt
-rw-r--r--  1 lisowskiw  staff  555 May 24 15:08 Status_Non_DoD_1975_December.txt
-rw-r--r--  1 lisowskiw  staff  555 May 24 15:08 Status_Non_DoD_1975_June.txt
-rw-r--r--  1 lisowskiw  staff  555 May 24 15:08 Status_Non_DoD_1975_March.txt
-rw-r--r--  1 lisowskiw  staff  555 May 24 15:08 Status_Non_DoD_1975_September.txt
-rw-r--r--  1 lisowskiw  staff  555 May 24 15:08 Status_Non_DoD_1976_December.txt
-rw-r--r--  1 lisowskiw  staff  555 May 24 15:08 Status_Non_DoD_1976_June.txt
-rw-r--r--  1 lisowskiw  staff  555 May 24 15:08 Status_Non_DoD_1976_March.txt
-rw-r--r--  1 lisowskiw  staff  555 May 24 15:08 Status_Non_DoD_1976_September.txt

[Greg Saldutte]

Thank you both Clyde and William.