topic Re: Defining a new variable using data from different rows in SAS Programming

Defining a new variable using data from different rows

mahler_ji — Mon, 17 Feb 2014 17:17:59 GMT

Hello All,

I have data that takes the following form:

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

Re: Defining a new variable using data from different rows

Peter_C — Mon, 17 Feb 2014 17:35:07 GMT

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

Re: Defining a new variable using data from different rows

Reeza — Mon, 17 Feb 2014 17:39:47 GMT

lag vs leg

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.

Re: Defining a new variable using data from different rows

PGStats — Mon, 17 Feb 2014 19:26:30 GMT

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 ;

Re: Defining a new variable using data from different rows

mahler_ji — Tue, 18 Feb 2014 00:18:22 GMT

, 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