Lag function to return value in first row

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

0101
Posts: 52
Joined: Fri Mar 04, 2011 9:42 pm

Lag function to return value in first row

Postby 0101 » Thu Aug 30, 2012 4:11 am

I have data like:

Code: Select all

Region Quarter Food    Count
--------------------------
West    1       Apples  10
West    1       Grapes  5
West    1       Figs    2
West    1       Carrots 3
--------------------------
West    2       Apples  30
West    2       Figs    1
West    2       Carrots 5
--------------------------
East    1       Figs    10
--------------------------
etc.
For each Region, on the first row of each Quarter, I need to return the count for Apples. I also need to return the sum of counts for Grapes and Figs. For example:

Code: Select all

Region Quarter Food    Count CountApples CountGrapesFigs
-------------------------------------------------------
West    1       Apples  10    10          7
West    1       Grapes  5
West    1       Figs    2
West    1       Carrots 3
-------------------------------------------------------
West    2       Apples  30    2           1
West    2       Figs    1
West    2       Carrots 5
-------------------------------------------------------
East    1       Figs    10                10
-------------------------------------------------------
I'm assuming the lag function is what I need, but am having trouble. Below is what I started but they fall short. For example, the food listed first for each Region and Quarter (like the two Apples and the Figs for East, Quarter 1) will get skipped.

* Doesn't return anything for Apples because Apples happens to be the first item in each Region and Quarter.

Code: Select all

IF Region = lag(Region) AND Quarter = lag(Quarter) AND Food = "Apples" CountApples = Count.
execute.
* Returns the count for Grapes and Count for Figs but not in the first row of the quarter (and of course doesn't sum them).

Code: Select all

IF Region = lag(Region) AND Quarter = lag(Quarter) AND (Food = "Grapes" OR Food = "Figs") CountGrapesFigs = Count.
execute.
Any suggestions? Sample data attached if it helps. Thank you.
You do not have the required permissions to view the files attached to this post.
Penguin_Knight
Posts: 473
Joined: Thu Apr 05, 2012 5:58 pm

Re: Lag function to return value in first row

Postby Penguin_Knight » Thu Aug 30, 2012 11:17 am

Code: Select all

SORT CASES BY Region(A) Quarter(A).

COMPUTE uniqueID = Region * 10000 + Quarter .
COMPUTE apple = (Food = "Apples")*Count .
COMPUTE figGrape = (Food = "Figs" | Food = "Grapes")*Count .
EXECUTE .

AGGREGATE
  /OUTFILE=* MODE=ADDVARIABLES
  /BREAK=uniqueID
  /CountApples =SUM(apple) 
  /CountGrapesFigs=SUM(figGrape).

DO IF (uniqueID = lag(uniqueID)).
RECODE CountApples  (ELSE=SYSMIS).
RECODE CountGrapesFigs (ELSE=SYSMIS).
END IF.
EXECUTE.

DELETE VARIABLES uniqueID apple figGrape .
EXECUTE .
0101
Posts: 52
Joined: Fri Mar 04, 2011 9:42 pm

Re: Lag function to return value in first row

Postby 0101 » Fri Sep 14, 2012 9:10 pm

Just wanted to reply to say this worked out great. Thank you.

Who is online

Users browsing this forum: No registered users and 2 guests

cron