Defining a new variable using data from different rows

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 101
Accepted Solution

Defining a new variable using data from different rows

Hello All,

I have data that takes the following form:

Screen shot 2014-02-17 at 12.14.21 PM.png

And I am trying to create a variable that defines the following equation:

Variable_j,s = [Assets_j,s - Assets_j,s-1 * (1 + Returns_j,s-1)] / Assets_j,s-1

where j represents firm number and s represents the month.  The data above only shows firm 4, but I have lots of data like this for lots of firms. 

So how can I create a new column with this information for every month?  The equation includes data from the current month as well as data from the prior month.

Any help would be greatly appreciated!!

Thanks,

John


Accepted Solutions
Solution
‎02-17-2014 02:26 PM
Respected Advisor
Posts: 4,654

Re: Defining a new variable using data from different rows

Posting data instead of a picture would have allowed some testing and prevented typos

data performance ;

set input_data ;

by fund;

old_ass = lag( assets ) ;

old_ret = lag( return ) ;

if first.fund then Variable = . ;

else Variable = {Assets - old_Ass * (1 + old_ret ) ) / old_Ass ;

drop old: ;

run ;


PG

PG

View solution in original post


All Replies
Valued Guide
Posts: 2,175

Re: Defining a new variable using data from different rows

depending on column names, something like this might work

data performance ;

set input_data ;

by fund month ;

old_ass = lag( assets ) ;

old_ret = lag( return ) ;

if first.firm then  Variable = . ;

else Variable = {Assets - old_Ass * (1 + old_ret ) ) / old_Ass ;

drop old_ass old_ret ;

run ;

Message was edited by: Peter Crawford after review by @reeza

Super User
Posts: 17,868

Re: Defining a new variable using data from different rows

lag vs leg Smiley Happy

Look up the lag and retain functions.

In SAS arrays work differently than other languages, so you'll actually need to use different constructs to get what you need.

Solution
‎02-17-2014 02:26 PM
Respected Advisor
Posts: 4,654

Re: Defining a new variable using data from different rows

Posting data instead of a picture would have allowed some testing and prevented typos

data performance ;

set input_data ;

by fund;

old_ass = lag( assets ) ;

old_ret = lag( return ) ;

if first.fund then Variable = . ;

else Variable = {Assets - old_Ass * (1 + old_ret ) ) / old_Ass ;

drop old: ;

run ;


PG

PG
Frequent Contributor
Posts: 101

Re: Defining a new variable using data from different rows

, and ,

Thank you so much for your help!  I will try to post data next time, so that you all can have more to work with.

This is working great, and I will let you know if I have any more issues.

I am so thankful to have this support community as I continue to learn to use SAS!

John

☑ This topic is SOLVED.

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

Discussion stats
  • 4 replies
  • 261 views
  • 0 likes
  • 4 in conversation