topic Re: Making calculations across rows in SAS Data Management

Making calculations across rows

pablorodriguez1 — Thu, 16 Mar 2017 12:35:46 GMT

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!

Re: Making calculations across rows

Reeza — Thu, 16 Mar 2017 12:45:51 GMT

Are you using data integratation studio?

Re: Making calculations across rows

Kurt_Bremser — Thu, 16 Mar 2017 12:46:12 GMT

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-data-AKA-generate/ta-p/258712 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).

Re: Making calculations across rows

rogerjdeangelis — Thu, 16 Mar 2017 14:04:54 GMT

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;
');

Re: Making calculations across rows

s_lassen — Wed, 22 Mar 2017 15:28:24 GMT

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.