Generating Quarters from date variable

[Nihar Singh]

Hi All,

I want to generate quarters in my data set. I have my date variable in the D-M-Y format. I have used the following commands to separate date, month, and calendar year but could not figure out how to generate quarters-

split co_stkdate, parse(-) destring
rename (co_stkdate?) (date2 month cal_year)
gen f_year= cal_year if(month <= 3)
replace f_year = (cal_year + 1) if(month >3)
gen ddate = daily(co_stkdate, "DMY")
format ddate %td

Dataex-

* Example generated by -dataex-. For more info, type help dataex
clear
input long co_code str66 company_name str10 co_stkdate byte(date2 month) int cal_year float(f_year ddate)
11 "20 MICRONS LTD." "06-10-2008"  6 10 2008 2009 17811
11 "20 MICRONS LTD." "07-10-2008"  7 10 2008 2009 17812
11 "20 MICRONS LTD." "08-10-2008"  8 10 2008 2009 17813
11 "20 MICRONS LTD." "10-10-2008" 10 10 2008 2009 17815
11 "20 MICRONS LTD." "13-10-2008" 13 10 2008 2009 17818
11 "20 MICRONS LTD." "14-10-2008" 14 10 2008 2009 17819
11 "20 MICRONS LTD." "15-10-2008" 15 10 2008 2009 17820
11 "20 MICRONS LTD." "16-10-2008" 16 10 2008 2009 17821
11 "20 MICRONS LTD." "17-10-2008" 17 10 2008 2009 17822
11 "20 MICRONS LTD." "20-10-2008" 20 10 2008 2009 17825
11 "20 MICRONS LTD." "21-10-2008" 21 10 2008 2009 17826
11 "20 MICRONS LTD." "22-10-2008" 22 10 2008 2009 17827
11 "20 MICRONS LTD." "23-10-2008" 23 10 2008 2009 17828
11 "20 MICRONS LTD." "24-10-2008" 24 10 2008 2009 17829
11 "20 MICRONS LTD." "27-10-2008" 27 10 2008 2009 17832
11 "20 MICRONS LTD." "28-10-2008" 28 10 2008 2009 17833
11 "20 MICRONS LTD." "29-10-2008" 29 10 2008 2009 17834
11 "20 MICRONS LTD." "31-10-2008" 31 10 2008 2009 17836
11 "20 MICRONS LTD." "03-11-2008"  3 11 2008 2009 17839
11 "20 MICRONS LTD." "04-11-2008"  4 11 2008 2009 17840
11 "20 MICRONS LTD." "05-11-2008"  5 11 2008 2009 17841
11 "20 MICRONS LTD." "06-11-2008"  6 11 2008 2009 17842
11 "20 MICRONS LTD." "07-11-2008"  7 11 2008 2009 17843
11 "20 MICRONS LTD." "10-11-2008" 10 11 2008 2009 17846
11 "20 MICRONS LTD." "11-11-2008" 11 11 2008 2009 17847
11 "20 MICRONS LTD." "12-11-2008" 12 11 2008 2009 17848
11 "20 MICRONS LTD." "14-11-2008" 14 11 2008 2009 17850
11 "20 MICRONS LTD." "17-11-2008" 17 11 2008 2009 17853
11 "20 MICRONS LTD." "18-11-2008" 18 11 2008 2009 17854
11 "20 MICRONS LTD." "19-11-2008" 19 11 2008 2009 17855
11 "20 MICRONS LTD." "20-11-2008" 20 11 2008 2009 17856
11 "20 MICRONS LTD." "21-11-2008" 21 11 2008 2009 17857
11 "20 MICRONS LTD." "24-11-2008" 24 11 2008 2009 17860
11 "20 MICRONS LTD." "25-11-2008" 25 11 2008 2009 17861
11 "20 MICRONS LTD." "26-11-2008" 26 11 2008 2009 17862
11 "20 MICRONS LTD." "28-11-2008" 28 11 2008 2009 17864
11 "20 MICRONS LTD." "01-12-2008"  1 12 2008 2009 17867
11 "20 MICRONS LTD." "02-12-2008"  2 12 2008 2009 17868
11 "20 MICRONS LTD." "03-12-2008"  3 12 2008 2009 17869
11 "20 MICRONS LTD." "04-12-2008"  4 12 2008 2009 17870
11 "20 MICRONS LTD." "05-12-2008"  5 12 2008 2009 17871
11 "20 MICRONS LTD." "08-12-2008"  8 12 2008 2009 17874
11 "20 MICRONS LTD." "10-12-2008" 10 12 2008 2009 17876
11 "20 MICRONS LTD." "11-12-2008" 11 12 2008 2009 17877
11 "20 MICRONS LTD." "12-12-2008" 12 12 2008 2009 17878
11 "20 MICRONS LTD." "15-12-2008" 15 12 2008 2009 17881
11 "20 MICRONS LTD." "16-12-2008" 16 12 2008 2009 17882
11 "20 MICRONS LTD." "17-12-2008" 17 12 2008 2009 17883
11 "20 MICRONS LTD." "18-12-2008" 18 12 2008 2009 17884
11 "20 MICRONS LTD." "19-12-2008" 19 12 2008 2009 17885
11 "20 MICRONS LTD." "22-12-2008" 22 12 2008 2009 17888
11 "20 MICRONS LTD." "23-12-2008" 23 12 2008 2009 17889
11 "20 MICRONS LTD." "24-12-2008" 24 12 2008 2009 17890
11 "20 MICRONS LTD." "26-12-2008" 26 12 2008 2009 17892
11 "20 MICRONS LTD." "29-12-2008" 29 12 2008 2009 17895
11 "20 MICRONS LTD." "30-12-2008" 30 12 2008 2009 17896
11 "20 MICRONS LTD." "31-12-2008" 31 12 2008 2009 17897
11 "20 MICRONS LTD." "01-01-2009"  1  1 2009 2009 17898
11 "20 MICRONS LTD." "02-01-2009"  2  1 2009 2009 17899
11 "20 MICRONS LTD." "05-01-2009"  5  1 2009 2009 17902
11 "20 MICRONS LTD." "06-01-2009"  6  1 2009 2009 17903
11 "20 MICRONS LTD." "07-01-2009"  7  1 2009 2009 17904
11 "20 MICRONS LTD." "09-01-2009"  9  1 2009 2009 17906
11 "20 MICRONS LTD." "12-01-2009" 12  1 2009 2009 17909
11 "20 MICRONS LTD." "13-01-2009" 13  1 2009 2009 17910
11 "20 MICRONS LTD." "14-01-2009" 14  1 2009 2009 17911
11 "20 MICRONS LTD." "15-01-2009" 15  1 2009 2009 17912
11 "20 MICRONS LTD." "16-01-2009" 16  1 2009 2009 17913
11 "20 MICRONS LTD." "19-01-2009" 19  1 2009 2009 17916
11 "20 MICRONS LTD." "20-01-2009" 20  1 2009 2009 17917
11 "20 MICRONS LTD." "21-01-2009" 21  1 2009 2009 17918
11 "20 MICRONS LTD." "22-01-2009" 22  1 2009 2009 17919
11 "20 MICRONS LTD." "23-01-2009" 23  1 2009 2009 17920
11 "20 MICRONS LTD." "27-01-2009" 27  1 2009 2009 17924
11 "20 MICRONS LTD." "28-01-2009" 28  1 2009 2009 17925
11 "20 MICRONS LTD." "29-01-2009" 29  1 2009 2009 17926
11 "20 MICRONS LTD." "30-01-2009" 30  1 2009 2009 17927
11 "20 MICRONS LTD." "02-02-2009"  2  2 2009 2009 17930
11 "20 MICRONS LTD." "03-02-2009"  3  2 2009 2009 17931
11 "20 MICRONS LTD." "04-02-2009"  4  2 2009 2009 17932
11 "20 MICRONS LTD." "05-02-2009"  5  2 2009 2009 17933
11 "20 MICRONS LTD." "06-02-2009"  6  2 2009 2009 17934
11 "20 MICRONS LTD." "09-02-2009"  9  2 2009 2009 17937
11 "20 MICRONS LTD." "10-02-2009" 10  2 2009 2009 17938
11 "20 MICRONS LTD." "11-02-2009" 11  2 2009 2009 17939
11 "20 MICRONS LTD." "12-02-2009" 12  2 2009 2009 17940
11 "20 MICRONS LTD." "13-02-2009" 13  2 2009 2009 17941
11 "20 MICRONS LTD." "16-02-2009" 16  2 2009 2009 17944
11 "20 MICRONS LTD." "17-02-2009" 17  2 2009 2009 17945
11 "20 MICRONS LTD." "18-02-2009" 18  2 2009 2009 17946
11 "20 MICRONS LTD." "19-02-2009" 19  2 2009 2009 17947
11 "20 MICRONS LTD." "20-02-2009" 20  2 2009 2009 17948
11 "20 MICRONS LTD." "24-02-2009" 24  2 2009 2009 17952
11 "20 MICRONS LTD." "25-02-2009" 25  2 2009 2009 17953
11 "20 MICRONS LTD." "26-02-2009" 26  2 2009 2009 17954
11 "20 MICRONS LTD." "27-02-2009" 27  2 2009 2009 17955
11 "20 MICRONS LTD." "02-03-2009"  2  3 2009 2009 17958
11 "20 MICRONS LTD." "03-03-2009"  3  3 2009 2009 17959
11 "20 MICRONS LTD." "04-03-2009"  4  3 2009 2009 17960
11 "20 MICRONS LTD." "05-03-2009"  5  3 2009 2009 17961
end
format %td ddate

------------------ copy up to and including the previous line ------------------

Listed 100 out of 16342108 observations

[Nick Cox]

Thanks for the data example. split can be useful here but a more direct route runs through

help datetime

which tells you most of what you ever need to know about handling dates.. Here is some technique:

* Example generated by -dataex-. For more info, type help dataex
clear
input str10 co_stkdate byte(date2 month) int cal_year float(f_year ddate)
"06-10-2008"  6 10 2008 2009 17811
"03-11-2008"  3 11 2008 2009 17839
"01-12-2008"  1 12 2008 2009 17867
"01-01-2009"  1  1 2009 2009 17898
"03-02-2009"  3  2 2009 2009 17931
"02-03-2009"  2  3 2009 2009 17958
end
format %td ddate

gen qdate1 = qofd(ddate)
gen qdate2 = qofd(daily(co_stkdate, "DMY"))

gen quarter = quarter(ddate)

list , sepby(cal_year)


     +----------------------------------------------------------------------------------------+
     | co_stkdate   date2   month   cal_year   f_year       ddate   qdate1   qdate2   quarter |
     |----------------------------------------------------------------------------------------|
  1. | 06-10-2008       6      10       2008     2009   06oct2008      195      195         4 |
  2. | 03-11-2008       3      11       2008     2009   03nov2008      195      195         4 |
  3. | 01-12-2008       1      12       2008     2009   01dec2008      195      195         4 |
     |----------------------------------------------------------------------------------------|
  4. | 01-01-2009       1       1       2009     2009   01jan2009      196      196         1 |
  5. | 03-02-2009       3       2       2009     2009   03feb2009      196      196         1 |
  6. | 02-03-2009       2       3       2009     2009   02mar2009      196      196         1 |
     +----------------------------------------------------------------------------------------+

[Nihar Singh]

Thank you, Nick.

[Nihar Singh]

Nick- I tried generating a variable yq (year quarter) through the command-

gen yq= yq(cal_year, quarter)
format yq %tm

However, instead of taking generating yq, stata has generated ym variable that too from 1972.

[Nihar Singh]

Got to know my mistake. Instead of %tm, I need to use %tq.

[Nick Cox]

First off, my code in #2 already calculated a quarterly date variable for you -- in two different ways.

Second off, if you tell Stata that what is a quarterly date variable for you is really a monthly date variable then Stata is going to believe you, and you are going to be confused. Otherwise put: look again to see that you specified a wrong displayformat for what you want.

Type these commands and then look at the results.

di yq(1996, 1) 

di %tq yq(1996, 1) 

di %tm yq(1996, 1)

To Stata 144 is just a number. If you say "this is a quarterly date" you will see a result that makes sense, but not otherwise.

[Nihar Singh]

Yes, I will make sure I clearly state what I want. Thank you, Nick!

[Nick Cox]

#6 was fairly fast -- an answer 7 minutes after the question -- but as many people will know new posts aren't visible while you are writing an answer. Hence I missed #5.