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.
Original Message:
Sent: Mon June 17, 2024 07:42 PM
From: Jon Peck
Subject: How to create a moving average dummy
Sorry. I really don't understand what you are doing or what you want to do with those new variables, but I would have expected you to use CREATE to do the moving averages. SHIFT VALUES is another command that might be useful.
One more thing: get rid of all those EXECUTE commands. They are doing nothing and just force unnecessary data passes.
--
Original Message:
Sent: 6/17/2024 7:31:00 PM
From: Amanda Eriksson
Subject: RE: How to create a moving average dummy
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
Original Message:
Sent: Mon June 17, 2024 09:27 AM
From: Jon Peck
Subject: How to create a moving average dummy
You probably know about the LAG function already, but you might find the CREATE (Transform > Create Time Series) transformation command helpful.
--
Original Message:
Sent: 6/17/2024 1:00:00 AM
From: Amanda Eriksson
Subject: How to create a moving average dummy
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
------------------------------
Amanda Eriksson
------------------------------