How to add numbers that are NOT in the same row?

[adriannek]

Hi all,

I'm trying to add values from a dataset that are not in the same row. Specifically, I'm trying to calculate the stock return, using the beginning price on January 1, 2020 and ending price on December 1, 2020. The values that I'm trying to calculate can be seen in the file attached (highlighted with black and blue markers) Any suggestions? Thanks.  

[Tom]

Please post sample data as text, not attachments.

[adriannek]

Ok, below is a copy/paste of my imported dataset and I'm trying to calculate the difference between the numbers in bold – as you can see, they're located in different rows. How would I tackle this?: 

01JAN2015 94.910004 90.959999 .
01FEB2015 91.300003 104.080002 0.1442391792
01MAR2015 104.349998 104.889999 0.0077825283
01APR2015 105.43 108.720001 0.0365143577
01MAY2015 109.949997 110.370003 0.0151766496
01JUN2015 111.480003 114.139999 0.0341578766
01JUL2015 114.949997 120 0.0513404055
01AUG2015 120.879997 101.879997 -0.1460622
01SEP2015 99.309998 102.199997 0.0031409724
01OCT2015 102.970001 113.739998 0.1129158983
01NOV2015 114.489998 113.470001 -0.002373749
01DEC2015 114.150002 105.080002 -0.073940405

[ballardw]

Difference or sum? Your subject line says ADD. If you want a difference then you need to tell us what order to subtract and variable names would be nice to reference.

Is this always only Dec and Jan of the same year? Is there always both a Jan and Dec value?

If so, and if your values that appear to be dates actually are then something like this might work if the difference is the third column - the second.

data want;
   set have;
   lcol2 = lag11(col2);
   if month(date)=12 then difference = col3 - lcol2;
   drop lcol2;
run;

The LAG function can return a value from a previous row. Lag, or more formally, Lag1 references the value of a variable on one record previous, Lag2 2 records previous etc.

The above code will have missing values for the difference in records where the month is not Dec.

Warning: if you do not always have a Jan and Dec value with every month in between you can get incorrect results. If the Jan or any month prior to Dec is missing then the record 11 records before would be used and who knows what value that is; if Dec is missing no calculation performed; if your dates are not SAS numeric values with a date format applied then you need to create such OR parse the character value of the date to determine Dec records.

[adriannek]

Thanks for the suggestion. But my SAS Enterprise Guide that I use for my class does not have the LAG function (I believe because it's an older version), and hence I cannot execute what you suggested. Though it seems solid, do you have any other way to tackle this problem? Thanks!

[mkeintz]

---

@adriannek wrote:
Thanks for the suggestion. But my SAS Enterprise Guide that I use for my class does not have the LAG function (I believe because it's an older version), and hence I cannot execute what you suggested. Though it seems solid, do you have any other way to tackle this problem? Thanks!

---

Are you sure?  If your SAS Enterprise Guide offers the New Program option, then you certainly should be able to run a data step, which in turn means you certainly would have the lag function - and the related dif function, which is defined as dif(x)= x-lag(x).

Here is such a program that I ran in my Enterprise Guide:

proc sort data=sashelp.stocks out=have;
  by stock date;
run;
data monthly_returns;
  set have;
  by stock;
  return=dif(adjclose)/lag(adjclose);
  if first.stock then return=.;
run;

[ballardw]

---

@adriannek wrote:
Thanks for the suggestion. But my SAS Enterprise Guide that I use for my class does not have the LAG function (I believe because it's an older version), and hence I cannot execute what you suggested. Though it seems solid, do you have any other way to tackle this problem? Thanks!

---

Since the LAG function has been part of SAS coding for 30-odd years I suspect that you are attempting to use some widget in EG, possibly a query tool. The queries tend to be based on SQL which does not really have a concept of record order and so would not have the Lag function available. You may have to use a code box to enter actual code in the flow.

[mkeintz]

It may be that the OP has some missing months between Jan and Dec.  If so then lag11 might not work.  An alternative:

data want;
  set have;
  if month(date) in (1,12) then do;
    return=(close - lag(open))/lag(open);
    if month(date)=1 then return=.;
  run;

Since this only updates the fifo queue underlying the lag function for Jan and Dec, a single "period" lag works.  Of course, this assumes that there is always a January record between any 2 December records.

@adriannek: Do you really want the return from Open of Jan 1 through close of Dec 1?  Why not the return from close of Jan 1 through close of the next Jan 1?  (or Dec 1 through Dec 1).   Then there would be no gaps in your series of returns.