Hi,

I work in research and handle merged data files received monthly. Each data set includes approximately 1000 rows of data per month, covering a period of ~two years. Each month, the merged file is updated with the latest months data (an additional 1000 rows). 

I would like to create a few time variables and group the data by:

• Latest 3 months
• Rolling 3 months
• Rolling 6 months
• By quarter (following the calendar)

In the SPSS file, I have a variable named MONTH, which is numeric and sequentially labels each month (1 for the first month, 2 for the second, and so on). These months are labeled as Oct 2022 (1), Nov 2022 (2), up until the latest months May 2024 (20).

Currently, I manually create new variables each month. For instance, for a 3-month moving average, I create a new variable called Moving_average_20 (labeled as Moving average - May 2024) and use If\F statements to assign a value of 1 for the last three months and 0 otherwise.

I use a similar approach for the other time variables. Is there any SPSS syntax that can automate this process?

Many thanks

Thanks everyone!

I have tried both but I don't understand how to combine the time periods I want. My current script (for the 3 month roll dummy looks like this): 

Etc up to MA3_20 and then:

This is then used as a banner variable/crosstab in another software (Q).

With the current script I have to create a new variable, update the labels and variable level each month. Any suggestion on how to use the Lag command to make this easier?

Many thanks

------------------------------
Amanda Eriksson
------------------------------

Thanks Jon, I'll have a look at the CREATE function. Thanks for letting me know about the execute commands, I will remove them. 

I basically want to create multiple dummy variables that group the data into a 3 month roll. This is later used in another software called Q that use SPSS as a data source. So I'm not really creating a moving average, just groups to cut other variables by. See screenshot below. 

------------------------------
Amanda Eriksson
------------------------------

you should be able to use datediff for the 3 month rolling mean. 

or

you could also alter type the dates to strings parse them out alter type them back to numeric & work with them then

------------------------------
Art Jack
------------------------------

Not sure I understand the dummy part, but could use of Transform/Create Time Series in the menus or something like CREATE /ma_3=MA(value 3) in syntax solve things? The span for the averages can of course be changed.