convert daily data into weekly data

[Jessica Marques]

Hi all,

I have trouble in converting my daily data into weekly data.Could you please help me !!!
I tried with these following commands but it doesn't work at all.Please find attached the photo of my date variable.

. di dow(mdy(3,4,2012)) 0 and so gen dailydate = date(date, "DMY") gen Sundays = dailydate - dow(dailydate) collapse ... , by(Sundays) Thank you so much!

[Nick Cox]

Please review advice in the FAQ http://www.statalist.org/forums/help#stata

1. Photos are hard to read. Examples of real or realistic data are preferred.

2. Code should be presented using CODE delimiters.

3. "doesn't work" is explicitly warned against as just too vague.

It seems that you have daily dates with no Saturdays or Sundays. They can be still classified by the corresponding (earlier) Sundays and collapse could then be applied.

Demonstration:

. clear 

. set obs 30
obs was 0, now 30

. gen date = mdy(1,3,2000) + _n - 1

. format date %td

. drop if inlist(dow(date), 6, 0) 
(8 observations deleted)

. gen Sundays = date - dow(date)

. list

     +---------------------+
     |      date   Sundays |
     |---------------------|
  1. | 03jan2000     14611 |
  2. | 04jan2000     14611 |
  3. | 05jan2000     14611 |
  4. | 06jan2000     14611 |
  5. | 07jan2000     14611 |
     |---------------------|
  6. | 10jan2000     14618 |
  7. | 11jan2000     14618 |
  8. | 12jan2000     14618 |
  9. | 13jan2000     14618 |
 10. | 14jan2000     14618 |
     |---------------------|
 11. | 17jan2000     14625 |
 12. | 18jan2000     14625 |
 13. | 19jan2000     14625 |
 14. | 20jan2000     14625 |
 15. | 21jan2000     14625 |
     |---------------------|
 16. | 24jan2000     14632 |
 17. | 25jan2000     14632 |
 18. | 26jan2000     14632 |
 19. | 27jan2000     14632 |
 20. | 28jan2000     14632 |
     |---------------------|
 21. | 31jan2000     14639 |
 22. | 01feb2000     14639 |
     +---------------------+

[Jessica Marques]

Dear Nick,

Thank you so so much for your very quick response and valuable advice. I am new to this forum so i don't know about some rules.

Yes, you are right. I just realize my daily data does not include Saturdays and Sundays.

I applied your codes and then collapse but it informs “clist required” .If i use the code "collapse..., by (Sundays)" , it notifies " invalid syntax"

I might do something wrong.Could you please give me further recommendation?

I just want to convert my daily data into weekly data (any date of week is fine to me) so that i can analyse weekly observations.

Thank you so much!

[Nick Cox]

OK, but now the problem is that a collapse command has invalid syntax.

You need to specify at least one clist and the help for the command tells you what that means.

Alternatively if you are typing literal ... that is quite wrong.

In effect, you are saying to Stata "collapse the dataset by Sundays" and Stata is saying "but which results do you want produced?".

[Jessica Marques]

Dear Nick,

Thank you so much for your help!I tried to specify the variables in clist command in some ways but again the problem still persists and informs " clist required" .I think i am not doing in the correct way.I am really sorry for all troubles.

Attached is my data file.I really feel grateful if you can give me another advice!!!

Thank you so much.

[Nick Cox]

I can't say any more yet because you still won't give exact code. This is like reporting that your car or washing machine or pen doesn't work with no other details. Looking at the dataset will do no good whatsoever as I still won't know what you want to do.

Please review advice in the FAQ http://www.statalist.org/forums/help#stata again. Otherwise we cannot help.

[Jessica Marques]

Dear Nick,

Again thank you so much for your very quick reply.I am really sorry for all confusions.

Actually i want to examine the short-run and long-run relationship within each pair ( crude and can); (crude and mex);(crude and aus);(crude and uk) ;(crude and nor) at weekly frequency.As indicated from my data file, i have 6 variables crude,can,uk,aus,mex,nor .Unfortunately, i only have daily data from online source.That's why i have to convert the daily data into weekly data for empirical analysis.

I am using Stata 12.

After your codes above, I typed "clist crude can mex aus uk " and then " collapse", it informs "clist required". Alternatively when i type "clist" and then "collapse" , it also informs "clist required"

I am quite beginner to Stata so there are some commands to which i am really not familiar .

I am not sure if i provide sufficient information about the problem i am confronting or not..If not,could you please ask me more specific question

I am attaching my data file here again.

Thank you so much!

[Nick Cox]

clist is Stata jargon for what you feed to collapse. You don't type clist literally. In documentation, font is highly informative. Look at the examples in the help for collapse. None of them include any typing of clist.

This would be legal, I surmise, and would give weekly means. You need to calculate Sundays first.

collapse crude can mex aus uk, by(Sundays)

[Jessica Marques]

Dear Nick,

Thank you so much for your great help! Now the codes work fine to me.

However, I have another problem.My initial daily data has 3917 observations and there are many gaps because prices on Saturday and Sunday are not recorded. In some weeks, prices for only 4 days are collected , in other weeks, prices for 5 days are recorded.

After conversion, my weekly data contains 560 observations.However I assume the number of weekly observations for each variable should be more than 800.

May I ask if is there any way for me to fix it and get weekly data of more than 800 observations instead of 560 observations?

Below is what i entered into Stata.

 . gen date = mdy(1,3,2000) + _n - 1

. format date %td

. drop if inlist(dow(date), 6, 0)
(1119 observations deleted)

. gen Sundays = date - dow(date)

. collapse crude can mex aus uk, by(Sundays)

Could you please help!!

Thank you so much!

[William Lisowski]

The first three commands, which you appear to have copied from Nick's post #2 above, were Nick's attempt to generate sample data, since you posted a picture that he could not turn into data. The last two commands, when applied to your original data with the 3917 original dates, are what you need. Along with, perhaps,

format Sundays %td

[Jessica Marques]

Dear William and Nick,

Thank you for all great help!!Now i got it..Yeah, it is my mistake..I did not follow exactly Nick's advice.
Sorry for all trouble.

[Jessica Marques]

Dear Nick and William,

May I have another question concerning about date variable?

After converting daily data into weekly data, I ran Dickey Fuller test on each series or any other tests which require “date” format for time series, it always informs ”no observation”

However, if I convert daily data into monthly data, all Dickey Fuller tests and other tests work smoothly.

I might do something wrong or I miss some commands. Could you please help me fix it?

This is how i convert daily data into monthly data and later all the tests work fine

gen time = mofd(date)
collapse crude can mex aus uk nor, by(time)
format time %tm
tsset time
dfuller can, regress noconst

This is how I applied your codes to convert daily data into weekly data ,unfortunately it always notifies “No observation” after Dickey Fuller test command.The codes seem to be similar to the above but i have no idea why it always informs error.

gen time = date - dow(date)
collapse crude can mex aus uk nor, by(time)
 format time %td
tsset time
dfuller can, regress noconst
no observations

Thank you so much!!

[Nick Cox]

You used a trick here to get weeks classified by starting Sundays and you then applied a daily display format so the displayed dates make sense. But you really have weekly data. That display format, I guess, is fooling tsset into thinking you have daily data, but mostly gaps. I would try

tsset time, delta(7)

to make explicit what you are doing and then try dfuller again. You may need to clear your current setting.

[Jessica Marques]

Dear Nick,

Thank you so so much!The code works fine!!!