## Need help recalling data from a previous row

Solved
Occasional Contributor
Posts: 6

# Need help recalling data from a previous row

Hi,

I am new to SAS and learning basic stuff via NHL stats and games. I am doing it via individual years and testing a new point system. . So one year is work._2014_. The data is sorted by points, conference, and division in that order and it is correct for what I want. Now, I need to add a games back column. I cannot for the life of me find how. I am guessing it would be an if statement.

data _2014_;

set _2014_;

if Div=Div then do;

Again I do not know how to proceed or even if this is the best way to complete this. The program needs to recall the row above it if it is in the same conference, then subtract the higher one from the lower one. Also, between divisions would be negative so you could delete or replace negative values with 0. Any information will be a huge help.

Thank you

Accepted Solutions
Solution
‎03-24-2015 10:40 AM
Super User
Posts: 13,583

## Re: Need help recalling data from a previous row

Provide a few example records of your existing data and then an example using that data to show the desired outcome.

The general function you are looking for is LAG: DivLastRecord = lag(Div); for example.

This may also involve the concept of FIRST. and LAST. processing since it looks like you don't want to do things across conference and division.

A note on learning coding: The

Data _2014_;

set _2014_;

while syntactically correct may lead you into difficulty determining where something goes wrong with a program. I would suggest not using it until you are very familiar with coding.

After your first example data it looks like you want Retain with first.

Data _2014_want;

set _2014_;

Retain basepoints; /* Basepoints is going to hold the value of points for the first team in the conference and division*/

by conference div; /* data should be sorted by those two as the main order*/

if first.div then basepoints= points;

Point difference = basepoints - points;

run;

All Replies
Occasional Contributor
Posts: 19

## Re: Need help recalling data from a previous row

Can you please write it clearly of what you want to do... I could actually not able to understand.

Occasional Contributor
Posts: 6

## Re: Need help recalling data from a previous row

 East Atlantic Boston Bruins 275 117 East Atlantic Tampa Bay Lightning 223 101 East Atlantic Montreal Canadiens 222 100 East Atlantic Detroit Red Wings 205 93 East Atlantic Ottawa Senators 189 88 East Atlantic Toronto Maple Leafs 179 84 East Atlantic Florida Panthers 143 66 East Atlantic Buffalo Sabres 103 52 East Metro Pittsburgh Penguins 247 109 East Metro New York Rangers 224 96 East Metro Philadelphia Flyers 218 94 East Metro Columbus Blue Jackets 211 93 East Metro New Jersey Devils 197 88 East Metro Washington Capitals 191 90 East Metro Carolina Hurricanes 187 83 East Metro New York Islanders 165 79

So now I want to add 2 columns for Points behind. So for the Metro div it would be Pit-New=Points behind (the points are the numbers). Pit needs to be 0 because they are in first place. Once I get Points behind I will be able to make it games behind myself (I hope).

Solution
‎03-24-2015 10:40 AM
Super User
Posts: 13,583

## Re: Need help recalling data from a previous row

Provide a few example records of your existing data and then an example using that data to show the desired outcome.

The general function you are looking for is LAG: DivLastRecord = lag(Div); for example.

This may also involve the concept of FIRST. and LAST. processing since it looks like you don't want to do things across conference and division.

A note on learning coding: The

Data _2014_;

set _2014_;

while syntactically correct may lead you into difficulty determining where something goes wrong with a program. I would suggest not using it until you are very familiar with coding.

After your first example data it looks like you want Retain with first.

Data _2014_want;

set _2014_;

Retain basepoints; /* Basepoints is going to hold the value of points for the first team in the conference and division*/

by conference div; /* data should be sorted by those two as the main order*/

if first.div then basepoints= points;

Point difference = basepoints - points;

run;

Occasional Contributor
Posts: 6

## Re: Need help recalling data from a previous row

I dont know why it did not place the labels. The table above in the response to the first has the syntax of

proc report data=_2014_;

column Conferance Div Team Overalb Overalo;

title1 'Season Totals';

title2 '2014';

run;

 East Atlantic Boston Bruins 275 117 East Atlantic Tampa Bay Lightning 223 101 East Atlantic Montreal Canadiens 222 100 East Atlantic Detroit Red Wings 205 93 East Atlantic Ottawa Senators 189 88 East Atlantic Toronto Maple Leafs 179 84 East Atlantic Florida Panthers 143 66 East Atlantic Buffalo Sabres 103 52 East Metro Pittsburgh Penguins 247 109 East Metro New York Rangers 224 96 East Metro Philadelphia Flyers 218 94 East Metro Columbus Blue Jackets 211 93 East Metro New Jersey Devils 197 88 East Metro Washington Capitals 191 90 East Metro Carolina Hurricanes 187 83 East Metro New York Islanders 165 7
Occasional Contributor
Posts: 19

## Re: Need help recalling data from a previous row

Lag is the best function you can use to take the previous value.

lag(variable)

Occasional Contributor
Posts: 6

## Re: Need help recalling data from a previous row

Thank you both so much. The retain worked and was what I needed. The lag is correct to and I am going to spend the next few hours playing with that. Have a great day and thank you again.

🔒 This topic is solved and locked.