Need help recalling data from a previous row

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 6
Accepted Solution

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: 10,500

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;

View solution in original post


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

EastAtlanticBoston Bruins275117
EastAtlanticTampa Bay Lightning223101
EastAtlanticMontreal Canadiens222100
EastAtlanticDetroit Red Wings20593
EastAtlanticOttawa Senators18988
EastAtlanticToronto Maple Leafs17984
EastAtlanticFlorida Panthers14366
EastAtlanticBuffalo Sabres10352
EastMetroPittsburgh Penguins247109
EastMetroNew York Rangers22496
EastMetroPhiladelphia Flyers21894
EastMetroColumbus Blue Jackets21193
EastMetroNew Jersey Devils19788
EastMetroWashington Capitals19190
EastMetroCarolina Hurricanes18783
EastMetroNew York Islanders16579

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: 10,500

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;

EastAtlanticBoston Bruins275117
EastAtlanticTampa Bay Lightning223101
EastAtlanticMontreal Canadiens222100
EastAtlanticDetroit Red Wings20593
EastAtlanticOttawa Senators18988
EastAtlanticToronto Maple Leafs17984
EastAtlanticFlorida Panthers14366
EastAtlanticBuffalo Sabres10352
EastMetroPittsburgh Penguins247109
EastMetroNew York Rangers22496
EastMetroPhiladelphia Flyers21894
EastMetroColumbus Blue Jackets21193
EastMetroNew Jersey Devils19788
EastMetroWashington Capitals19190
EastMetroCarolina Hurricanes18783
EastMetroNew York Islanders1657
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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 278 views
  • 6 likes
  • 3 in conversation