SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Making calculations across rows

Reply
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

Thanks in advance!

Super User
Posts: 19,862

Re: Making calculations across rows

Posted in reply to pablorodriguez1

Are you using data integratation studio?

Super User
Posts: 7,854

Re: Making calculations across rows

Posted in reply to pablorodriguez1

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
Valued Guide
Posts: 505

Re: Making calculations across rows

Posted in reply to KurtBremser
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<-read_sas("d:/sd1/class.sas7bdat");
have;
res<-have$Height[have$Name=="Alice"]/have$Weight[have$Name=="William"];
res;
endsubmit;
');

PROC Star
Posts: 102

Re: Making calculations across rows

Posted in reply to pablorodriguez1

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.

Ask a Question
Discussion stats
  • 4 replies
  • 505 views
  • 0 likes
  • 5 in conversation