Sorting data

[Zuhumnan Dapel]

Dear All,
I have a data:

 
Month
1974
1975
1976
1977
1978
1979
1980
.
.
.
2011

January
0.6579
0.6158
0.6244
0.631
0.6374
0.6475
0.5563



151.5455

February
0.6579
0.6146
0.6236
0.631
0.6475
0.5482
0.5482
.
.
.
151.9391

March
0.6579
0.6138
0.6246
0.637
0.6203
0.6396
0.5591



152.5074

April
0.6579
0.6124
0.625
0.65
0.6119
0.6212
0.5611
.
.
.
153.9673

May
0.6162
0.6124
0.625
0.6513
0.6116
0.6159
0.5611



154.8009

June
0.6162
0.6077
0.6262
0.6513
0.619
0.6055
0.5443
.
.
.
154.5029

July
0.6162
0.6077
0.6266
0.6513
0.6058
0.59
0.5431



151.8636

August
0.6162
0.6113
0.6265
0.6513
0.5892
0.5802
0.5443
.
.
.
152.7154

September
0.6135
0.6178
0.6265
0.6513
0.5892
0.5749
0.5338



155.2636

October
0.6162
0.6253
0.628
0.6513
0.5699
0.5753
0.5314
.
.
.
153.2569

November
0.6162
0.6257
0.631
0.6513
0.5847
0.5803
0.5317



155.7693

December
0.6162
0.6257
0.631
0.6513
0.5849
0.5703
0.5419
.
.
.
158.2074

How do I collapse this into a single column to form a time series format, e.g. 1974Q1, 1974Q2, ...., 2011Q4?

Thanks,
Dapel

[William Lisowski]

A better place to start would be to read some of your sample data into Stata (just two or three years) and use dataex from SSC as described in the Statalist FAQ to prepare the data for those who might wish to help you.

With that said, the following (which uses Nick Cox's excellent numdate command from SSC) shows how to turn data similar to yours into a time series of monthly observations. Going from monthly to quarterly depends on how you want to use your monthly values to create quarterly values, which you didn't describe.

// set up sample data
* Example generated by -dataex-. To install: ssc install dataex
clear
input str12 Month float(y1974 y1975 y1976)
"January"   .6579 .6158 .6244
"February"  .6579 .6146 .6236
"March"     .6579 .6138 .6246
"April"     .6579 .6124  .625
"May"       .6162 .6124  .625
"June"      .6162 .6077 .6262
"July"      .6162 .6077 .6266
"August"    .6162 .6113 .6265
"September" .6135 .6178 .6265
"October"   .6162 .6253  .628
"November"  .6162 .6257  .631
"December"  .6162 .6257  .631
end

// do the job
generate id = _n
reshape long y, i(id) j(Year)
numdate tm Date = Month Year, pattern(MY)
drop Month Year id
sort Date
list, clean noobs

        y      Date  
    .6579    1974m1  
    .6579    1974m2  
    .6579    1974m3  
    .6579    1974m4  
    .6162    1974m5  
    .6162    1974m6  
    .6162    1974m7  
    .6162    1974m8  
    .6135    1974m9  
    .6162   1974m10  
    .6162   1974m11  
    .6162   1974m12  
    .6158    1975m1  
    .6146    1975m2  
    .6138    1975m3  
... and so forth

[Zuhumnan Dapel]

This is amazing! It worked perfectly. Thank you very much!

Dapel

[Zuhumnan Dapel]

Again, I have a variables daily rates and want collapse into monthly rates.

rateyear    month    rate    month
2012    January    156.35    1
2012    January    156.2    1
2012    January    156.35    1
2012    January    156.35    1
2012    January    156.35    1
2012    January    156.55    1
2012    January    156.21    1
2012    January    156.5    1
2012    January    156.2    1
2012    January    156.33    1
2012    January    156.2    1
2012    January    156.2    1
2012    January    156.35    1
2012    January    156.33    1
2012    January    156.5    1
2012    January    156.2    1
2012    January    156.21    1
2012    January    156.55    1
2012    January    156.2    1
2012    January    156.21    1
2012    January    156.35    1
2012    February    155.4    2
2012    February    156.2    2
2012    February    156    2
2012    February    156    2
2012    February    155.6    2
2012    February    155.4    2
2012    February    155.4    2
2012    February    155.9    2
2012    February    156    2
2012    February    155.9    2
2012    February    155.4    2
2012    February    156.2    2
2012    February    156    2
2012    February    156.2    2
2012    February    155.4    2
2012    February    156    2
2012    February    155.4    2
2012    February    156.2    2
2012    February    155.6    2
2012    March    155.51    3
2012    March    155.56    3
2012    March    155.56    3
2012    March    155.51    3
2012    March    155.51    3
2012    March    155.56    3
2012    March    155.56    3
2012    March    155.56    3
2012    March    155.51    3
2012    March    155.56    3
2012    March    155.4    3
2012    March    155.56    3
2012    March    155.56    3
2012    March    155.51    3
2012    March    155.4    3
2012    March    155.4    3
2012    March    155.56    3
....
and so forth

How do I create a variable of month averages.

Thanks,
Dapel

[Nick Cox]

William has already explained very well how to construct a monthly date variable, after which

help collapse

[Zuhumnan Dapel]

Thanks. I found 24 pages with

help collapse

. Being trying

ascol tom()

but not getting along

[William Lisowski]

I belive that you typed, omitting the final letter "e",

help collaps

This fails to find a collaps command and instead returns the results of search collaps which includes 24 links. If you type

help collapse

you will receive instruction on Stata's collapse command.

In general, on Statalist, a recommendation to use the help command, rather than the search command, is intended to refer the reader to the documentation for a Stata command.

An alternative to the collapse command would be to use something like

by month: egen avg_rate = mean(rate)

but I haven't actually tried this approach in the absence of readily usable sample data.

[Zuhumnan Dapel]

Thank you William, it worked. I have one more challenge: is daily time series we are converting to monthly. Now that we have done so, how do one drop the daily rates so as to have justly the monthly (not repeated) values? This means the total number of observations will have to reduce.

Dapel

[Nick Cox]

#8 sounds exactly the question answered in #7. The most obvious solution is collapse; if that's not right for you you need to give us an example and/or an explanation of why not.

[William Lisowski]

In thanking me in #8, you give us no idea of what it is that I recommended that worked. But, I guess, if you had used help collapse to understand the collapse command, you would not now be asking for advice on reducing your data to one observation per month. So I assume you instead used the egen command, to generate the variable of month averages. I had only suggested that approach since it wasn't clear to me if you wanted to retain the daily values as well as the month averages. Since you didn't want the daily values, the collapse command would have gotten you directly to your objective.

From where you apparently are at now, you can identify one observation to keep for each month using egen's tag function.

egen keeper = tag(month)
keep if keeper
drop keeper

[Zuhumnan Dapel]

Much more clearer to me now. Thank you all very much. I was taking for the codes that enabled me generate the monthly averages but still retaining the daily rates.

[Zuhumnan Dapel]

Now, how do I drop the repeated observations and retain just one for each year and month? This is want I mean: we have now generated monthly averages from daily rates, such that we have many Januaries (equal the number of days), many Februaries, etc. I want to have only one January in each year, e.g Jan 2012, Jan 2013; Feb 2012; Feb 2013 and so on.

Thanks,
Dapel

[Zuhumnan Dapel]

I was able to get the situation squared out, this

egen keeper = tag(avg_rate rateyear month)

keep if keeper==1

gave me what I wanted.

Thanks,
Dapel