BookmarkSubscribeRSS Feed
pablorodriguez1
Calcite | Level 5

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!

4 REPLIES 4
Reeza
Super User

Are you using data integratation studio?

Kurt_Bremser
Super User

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).

rogerjdeangelis
Barite | Level 11
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;
');

s_lassen
Meteorite | Level 14

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

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