Conversion from monthly to quarterly data

[Muhammad Salahuddin]

Hi,

I have a longitudinal data set, which contains the monthly information. Required to convert from monthly to quarterly data. Data is, now, in the following format. How can I do it?

Any kind of advice appreciated.

input str134 B str4 D str12 E str38 Columns float ID
"B. (Mining And Quarrying)" "2007" "01-January" "63.7" 2
"B. (Mining And Quarrying)" "2007" "02-February" "61.5" 2
"B. (Mining And Quarrying)" "2007" "03-March" "67.7" 2
"B. (Mining And Quarrying)" "2007" "04-April" "73" 2
"B. (Mining And Quarrying)" "2007" "05-May" "78.59999999999999" 2
"B. (Mining And Quarrying)" "2007" "06-June" "80.8" 2
"B. (Mining And Quarrying)" "2007" "07-July" "86.59999999999999" 2

[Igor Paploski]

Try:

input str134 B str4 D str12 E str38 Columns float ID
"B. (Mining And Quarrying)" "2007" "01-January" "63.7" 2
"B. (Mining And Quarrying)" "2007" "02-February" "61.5" 2
"B. (Mining And Quarrying)" "2007" "03-March" "67.7" 2
"B. (Mining And Quarrying)" "2007" "04-April" "73" 2
"B. (Mining And Quarrying)" "2007" "05-May" "78.59999999999999" 2
"B. (Mining And Quarrying)" "2007" "06-June" "80.8" 2
"B. (Mining And Quarrying)" "2007" "07-July" "86.59999999999999" 2
end


gen month = substr(E,1,2)
destring month, replace
destring D, replace
gen qdate = qofd(dofm(ym(D, month)))
format %tq qdate

[Nick Cox]

Here is another way to do it. Not the question, but destring Columns is surely advisable too.

clear 
input str4 D str12 E 
"2007" "01-January" 
"2007" "02-February" 
"2007" "03-March" 
"2007" "04-April" 
"2007" "07-July" 
end

gen qdate = qofd(daily(D + substr(E, 4, .) + "1", "YMD")) 
format qdate %tq 

list 

     +-----------------------------+
     |    D             E    qdate |
     |-----------------------------|
  1. | 2007    01-January   2007q1 |
  2. | 2007   02-February   2007q1 |
  3. | 2007      03-March   2007q1 |
  4. | 2007      04-April   2007q2 |
  5. | 2007       07-July   2007q3 |
     +-----------------------------+

[Muhammad Salahuddin]

Try:

input str134 B str4 D str12 E str38 Columns float ID
"B. (Mining And Quarrying)" "2007" "01-January" "63.7" 2
"B. (Mining And Quarrying)" "2007" "02-February" "61.5" 2
"B. (Mining And Quarrying)" "2007" "03-March" "67.7" 2
"B. (Mining And Quarrying)" "2007" "04-April" "73" 2
"B. (Mining And Quarrying)" "2007" "05-May" "78.59999999999999" 2
"B. (Mining And Quarrying)" "2007" "06-June" "80.8" 2
"B. (Mining And Quarrying)" "2007" "07-July" "86.59999999999999" 2
end


gen month = substr(E,1,2)
destring month, replace
destring D, replace
gen qdate = qofd(dofm(ym(D, month)))
format %tq qdate

After applying the above code, data is, now, in the following format.
qdate
2007q1
2007q1
2007q1
2007q2
2007q2
2007q2

Still, it is not quarterly data. How can I do further?
Advance thanks.

[Nick Cox]

That's really up to you. Recipes that spring to mind are to collapse to the mean, the median, the total, the last non-missing value in each quarter and so on.

As your variable Columns is totally anonymous, I can't see how we are expected to advise. But the answer is likely to be some kind of collapse, by(qdate ID) or whatever else defines your panels.

[Muhammad Salahuddin]

That's really up to you. Recipes that spring to mind are to collapse to the mean, the median, the total, the last non-missing value in each quarter and so on.

As your variable Columns is totally anonymous, I can't see how we are expected to advise. But the answer is likely to be some kind of collapse, by(qdate ID) or whatever else defines your panels.

Hi Nick,

My data as following format. When I run the code "collapse, by (qdate ID)", it shows the "invalid syntax". I want to convert the mean of this monthly data to quarterly.

input str134 industries int D str12 E str38 production_index float ID byte month float qdate
"B. (Mining And Quarrying)" 2007 "01-January" "63.7" 2 1 188
"B. (Mining And Quarrying)" 2007 "02-February" "61.5" 2 2 188
"B. (Mining And Quarrying)" 2007 "03-March" "67.7" 2 3 188
"B. (Mining And Quarrying)" 2007 "04-April" "73" 2 4 189
"B. (Mining And Quarrying)" 2007 "05-May" "78.59999999999999" 2 5 189
"B. (Mining And Quarrying)" 2007 "06-June" "80.8" 2 6 189
"B. (Mining And Quarrying)" 2007 "07-July" "86.59999999999999" 2 7 190
"B. (Mining And Quarrying)" 2007 "08-August" "96.09999999999999" 2 8 190
"B. (Mining And Quarrying)" 2007 "09-September" "94.40000000000001" 2 9 190
"B. (Mining And Quarrying)" 2007 "10-October" "86.3" 2 10 191

[Nick Cox]

Sorry; when I said

some kind of collapse, by(qdate ID) or whatever else defines your panels

I didn't mean that code was the answer in terms of your complete code that you should type.

The whole point of #5 is that you need to decide exactly how to collapse your data and then build that into the command you type.

What is it that you want? Possibilities are the mean, the median, the total, the last non-missing value in each quarter and so on. If you are a student, ask your teacher or supervisor what should be done. If you are an independent researcher, it is your choice.