## 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

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

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
/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

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

### Who is online

Users browsing this forum: No registered users and 1 guest