## Making calculations across rows

Occasional Contributor
Posts: 5

# Making calculations across rows

Quick question:

In a table with lots of operations in which I have unique identifiers (concatenating date and operation ID), how can I choose to calculate the change between the amount of one month and the previous one? (Assuming that the rows are never next to each other since there are lots of different operations and dates).

Specifically, telling the code to take the amount of the row with a specific date and operation, and substract it from another specific row with another date and operation

Super User
Posts: 23,224

## Re: Making calculations across rows

Are you using data integratation studio?

Super User
Posts: 9,855

## Re: Making calculations across rows

First of all, are you trying to do that in DI Studio or just with manually written code?

(You might have posted into the wrong community, I can move your post if needed)

Second, please use the macro provided in https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... to convert your example data into a data step for posting here, and post it into a {i} or "little running man" window.

This allows us to recreate your data with a simple copy/paste and run.

There are several ways to achieve what you have in mind. One uses a sort that brings your observations into an order where the observations to be compared follow each other immediately; another uses a hash object to keep values to be compared in memory.

One can also use SQL to remerge the dataset with itself, depending on the proper condition.

Which option is best can be determined upon dataset structure and size (a hash object might run out of memory, SQL could get you dismal performance).

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Valued Guide
Posts: 505

## Re: Making calculations across rows

``````Divide Alice's Height by Williams Weight

This is what IML or IML interface with R is meant to solve

HAVE
====

Up to 40 obs from sd1.class total obs=19

Obs    NAME       SEX    AGE    HEIGHT    WEIGHT

1    Alfred      M      14     69.0      112.5
2    Alice       F      13     56.5*      84.0
-----
3    Barbara     F      13     65.3       98.0
4    Carol       F      14     62.8      102.5
5    Henry       M      14     63.5      102.5
6    James       M      12     57.3       83.0
7    Jane        F      12     59.8       84.5
8    Janet       F      15     62.5      112.5
9    Jeffrey     M      13     62.5       84.0
10    John        M      12     59.0       99.5
11    Joyce       F      11     51.3       50.5
12    Judy        F      14     64.3       90.0
13    Louise      F      12     56.3       77.0
14    Mary        F      15     66.5      112.0
15    Philip      M      16     72.0      150.0
16    Robert      M      12     64.8      128.0
17    Ronald      M      15     67.0      133.0
18    Thomas      M      11     57.5       85.0
19    William     M      15     66.5      112.0* WANT   56.5/112.0
-----

WANT

[1] 0.5044643

WORKING CODE

res<-have\$Height[have\$Name=="Alice"]/have\$Weight[have\$Name=="William"];

FULL SOLUTION

Cut and paste code into IML interface with R or use IML directly

%utl_submit_wps64('
libname sd1 "d:/sd1";
options set=R_HOME "C:/Program Files/R/R-3.3.2";
libname wrk "%sysfunc(pathname(work))";
libname hlp "C:\Program Files\SASHome\SASFoundation\9.4\core\sashelp";

data sd1.class;
set hlp.class;
run;quit;

proc r;
submit;
source("C:/Program Files/R/R-3.3.2/etc/Rprofile.site", echo=T);
library(haven);
have;
res<-have\$Height[have\$Name=="Alice"]/have\$Weight[have\$Name=="William"];
res;
endsubmit;
');

``````
PROC Star
Posts: 249

## Re: Making calculations across rows

Pablo,

I think an SQL solution could work:

``````proc sql;
select
current.ID,
intnx('MONTH',current.date,1) as StartOfMonth,
current.amount-previous.amount as ChangeAmount
from have current join have previous
on have.ID=previous.ID
and intck('MONTH',previous.date,current.date)=1
;``````

or something like that.

Regards,

Søren.

Discussion stats
• 4 replies
• 530 views
• 0 likes
• 5 in conversation