Rolling sample window with broken time period.

[jiangmi]

Hi, All

I just came up with this question while working on my stuffs.

I am creating a 3-month sampling window with one month lag to calculate the mean of the value.

I have 'year_mth' and 'value' in my data set. If I have 201408 data, then I can do a lag and assign the sampling window correctly.

However, in the case when 201408 data is missing, I assume lag function won't work. I think I will have to do the window assignment based on actual month and year, which caused me big headache.

Anybody had similar issue before or good idea?

Thanks.

Joe

data have;

input year_mth value;

cards;

201412 77

201411 65

201410 53

201409 29

201407 58

201406 62

;run;

[ballardw]

It depends on what you are doing with the LAGged values. Note that when looking at any thing with LAG that the first record has a missing value for the lagged variable, the first 2 if using lag2 and so forth.

Also I'm a little uncomfortable using lag in a decreasing sequence as your "date" runs as the interpretation gets a tad fuzzy.

Would there be any problem caused by having a record for 201408 but any other associated variables as missing (at least before this step)?

If you can tell us what your are doing with the next step we may also have additional ideas.

[jiangmi]

Thank, Ballardw.

I understand how lag function works and actually used it in one of my project to create lagged sampling windows. This question here came from that same project. Right now, the data I have is perfect, without any broken time period/missing period. Therefore, my code works fine. However, I did see some times there will be missing time periods in certain data. And if that happens, my code won't work. Thus, I am trying to make my code robust (or ready) to this kind of situations.

After observations are assigned to certain groups (G1-G5), an average of 'value' for each group will be calculated. An averges trend will be plotted after that. Notice that G3, G4, and G5 will only have two obs due to missing data. I hope this make sense.

Thanks again.

Joe

[KachiM]

The answer depends on how the groups G3, G4 and G5 will be used for computing means. The missing value could be taken as zero, and the denominator will be 2 for these Groups. Someone may argue that the denominator must always be 3 (in this case), and any missing value may be replaced by the just previous non-missing value.

What is the correct one?

[jiangmi]

In my case, if there are missing value, denominator will be 2 or less.

I worry more about putting the year_mth in the wrong group. e.g. G3 will always include 201410, 201409, and 201408. Although 201408 is missing in this case, I don't want to see 201407 to be put in G3. Every month you pull the new data out, the newest time period will always be on the top (201501 will be on the top if I pull the data today). The new G1 should be 201501, 201412, and 201411. The new G3 will be 201409, 201408, and 201407.

I am sorry if I still can not make my self understood or confuse you.

Thanks for your time.

Joe

[Reeza]

My suggestion would be to make sure that it doesn't happen. Add a step into the process to make sure your time series is unbroken. If a time period is missing then insert it with the missing value and then deal with it in your next step.