date aggregation

Moderators: statman, Analyst Techy, andris, Fierce, GerineL, Smash

sara b
Posts: 7
Joined: Fri Jan 09, 2009 2:23 pm

date aggregation

Postby sara b » Fri Jan 09, 2009 5:12 pm

Hello fellow SPSSers!
currently I have shift level data. There are three shifts per day (separate variable 1, 2, 3 for day, evening and night) The cases are arranged with date as:

date shift
01-Jul-2003 1
01-Jul-2003 2
01-Jul-2003 3
02-Jul-2003 1
02-Jul-2003 2
02-Jul-2003 3
03-Jul-2003 1
03-Jul-2003 2


I would like to aggregate this to the month for analysis. I am at my wits end trying to do this. Please send me a lifeline!

thanks!
Fierce
Moderator
Posts: 391
Joined: Sun Dec 28, 2008 5:55 pm
Location: Belo Horizonte, Brasil

Postby Fierce » Fri Jan 09, 2009 11:50 pm

At TRANSFORM>COMPUTE VARIABLE you can create a new variable and use the function <NEW VARIABLE> = XDATE.MONTH(<DATE VARIABLE>).
It will create a new variable representing the month with a number. You can then aggregate/split/select cases based on the new variable and do your analysis. If you have more than one year, you can do the same for the year and use both variables together and agreggate/split/select based on month and year.
sara b
Posts: 7
Joined: Fri Jan 09, 2009 2:23 pm

Postby sara b » Sat Jan 10, 2009 6:47 pm

You are brilliant! thank you! :D :D
sara b
Posts: 7
Joined: Fri Jan 09, 2009 2:23 pm

Postby sara b » Wed Jan 14, 2009 2:20 pm

that works month to month, but it is tedious to compare the months. Are there any other solutions? Is there a way to make the data set month level, versus shift level?
Fierce
Moderator
Posts: 391
Joined: Sun Dec 28, 2008 5:55 pm
Location: Belo Horizonte, Brasil

Postby Fierce » Wed Jan 14, 2009 2:43 pm

Hm... I thought the objective was to compare the months.
Now I could'nt understand the "make the dataset month level versus shift level"... What is the main objective?
sara b
Posts: 7
Joined: Fri Jan 09, 2009 2:23 pm

Postby sara b » Wed Jan 14, 2009 2:48 pm

Thanks for your quick response. Currently the data is at the shift level. with three shifts (1, 2, 3) per date. I want to aggregate up, combine or collapse the data to be at the month level.

Does this make better sense?
Fierce
Moderator
Posts: 391
Joined: Sun Dec 28, 2008 5:55 pm
Location: Belo Horizonte, Brasil

Postby Fierce » Fri Jan 16, 2009 7:41 pm

Couldn't get it all yet... May you want to work with data like one of the folowing...? Or am I completely, totally lost?

Code: Select all

date         shift
01-Jul-2003  123
02-Jul-2003  123
03-Jul-2003  123
or

Code: Select all

date      shift
Jul-2003  1
Jul-2003  2
Jul-2003  3
Aug-2003  1
Aug-2003  2
Aug-2003  3
or

Code: Select all

date      shift
Jul-2003  123
Aug-2003  123
Set-2003  123
sara b
Posts: 7
Joined: Fri Jan 09, 2009 2:23 pm

Postby sara b » Fri Jan 16, 2009 7:46 pm

I would like the data to be like the third choice. That is my goal.

thanks
Fierce
Moderator
Posts: 391
Joined: Sun Dec 28, 2008 5:55 pm
Location: Belo Horizonte, Brasil

Postby Fierce » Fri Jan 16, 2009 8:33 pm

First, just like before, create a variable to count the month - but now also a variable to count the day (in TRANSFORM>COMPUTE VARIABLE):
<MONTH VARIABLE> = XDATE.MONTH(<DATE VARIABLE>)
<DAY VARIABLE> = XDATE.MDAY(<DATE VARIABLE>)

Next, aggregate the other variables. Go to DATA>AGGREGATE, select the <MONTH VARIABLE> as Break Variable and paste all the other variables (except the ones created here and the date and shif variables) in the Summaries of Variables (and choose the function you want to use to aggregate - sum the values, take the mean.....).

Now your dataset is full of repeated values. To clean it, go to DATA>SELECT CASES>IF CONDITION IS SATISFIED and use the condition: "shift = 1 & <DAY VARIABLE> = 1" (all values are the same, this is just to remove the repeated cases). Also select the <COPY SELECTED CASES TO NEW DATASET> option and give any name to the new dataset. It will create a new, clean dataset.

After that you can delete the old variables and the ones that are useless now (like shift and the day and month variables just created).

To make it easy and faster, the following syntax does it all:

Code: Select all

COMPUTE monthvar=XDATE.MONTH(date).
EXECUTE.

COMPUTE dayvar=XDATE.MDAY(date).
EXECUTE.

AGGREGATE
  /OUTFILE=* MODE=ADDVARIABLES
  /BREAK=monthvar
  /<OTHERVARIABLE>_sum=SUM(<OTHERVARIABLE>)
  /<OTHERVARIABLE>_sum=SUM(<OTHERVARIABLE>)
  /<OTHERVARIABLE>_sum=SUM(<OTHERVARIABLE>).

DATASET COPY  NewDataset.
DATASET ACTIVATE  NewDataset.
FILTER OFF.
USE ALL.
SELECT IF (shift = 1 & dayvar = 1).
EXECUTE.

DELETE VARIABLES shift TO dayvar.
EXECUTE.
It will leave you with the date variables an all the aggregated varibales (and no repeated values).

If you have a lot of variables, it will be faster to use the menus in the AGGREGATE part. And remember to change the function (it's sum ehre, but I don't wich one you'll use - and you can use different functions for different variables).
pvalue
Posts: 36
Joined: Wed Nov 26, 2008 7:12 pm
Location: Kansas City, MO

Postby pvalue » Fri Jan 16, 2009 11:25 pm

Also, if the shift data and the date are combined in the same variable, you can you the SUBSTR function to compute a new variable with just the shift number.

Who is online

Users browsing this forum: No registered users and 1 guest

cron