BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
mahler_ji
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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

4 REPLIES 4
Peter_C
Rhodochrosite | Level 12

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

Reeza
Super User

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.

PGStats
Opal | Level 21

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
mahler_ji
Obsidian | Level 7

, 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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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