Calculate new variable based on data from different rows

Vic3 — Thu, 27 Jun 2024 20:26:28 GMT

Hello,

I want to calculate a new variable "VALUE" based on variables in the same and a previous rows of the dataset and do this by group. This screenshot from Excel makes it self explanatory.

VALUEn = (TIMEn - TIMEn-1) * average(RATEn:RATEn-1)

Additional problem is that the calculation has to start from the second row of each group.

I looked at relevant examples on this forum but couldn't make it work.

Thank you.

Re: Calculate new variable based on data from different rows

ballardw — Thu, 27 Jun 2024 20:38:44 GMT

Without data in the from of working data step code or expected results this is untested but may work if your data is sorted by group.

data want;
   set have;
   by group;
   diftime= dif(time);
   meanrate= mean(lag(rate),rate);
   if not (first.group) then value=diftime*meanrate;
   drop diftime meanrate;
run;

The DIF function is the difference of the current value of a variable from the previous observation in a data set on the Set statement. Lag returns the value from the previous observation.

Use of a BY statement creates automatic variables First. and Last. that indicate whether the current value is the first or last of a by group. These are numeric values of 1 (True) and 0 (False).

So this calculated the values needed using the current and last observation values of the rate and time but only calculated Value when it isn't the first row of a Group.

topic Calculate new variable based on data from different rows in SAS Programming

Calculate new variable based on data from different rows

Re: Calculate new variable based on data from different rows