topic Using two datasets to compute new values in SAS Enterprise Guide

Using two datasets to compute new values

eemrun — Mon, 16 Oct 2017 02:39:56 GMT

Simple data manipulation problemthat I just cannot get past! I have a dataset with the following variables:

A B C Iter

1 2 9 1

2 5 7 1

3 9 6 1

4 1 3 2

I also have another dataset with just one row of info.

A_A B_B C_C

1 3 7

what i need to do is the following:

For ITER = 1

First A_A_P1 = A_A + A -> 1 + 1 = 2

For subsequent ITER = 1

A_A_P1 = lag(A_A_P1) + A -> 2 + 2 = 4

So the A_A_P1 column should look like this:

A_A_P1

For iter = 2, same process but now the values will be under A_A_P2.

The same process will continue for B and C with respective ITERs.

Any idea how this can be done efficiently?

Re: Using two datasets to compute new values

Reeza — Mon, 16 Oct 2017 03:58:09 GMT

1. What would be the output for the sample data you've posted?

2. What code do you have so far?

Re: Using two datasets to compute new values

RW9 — Mon, 16 Oct 2017 09:38:18 GMT

Start by re-modelling your data so that it is in a simple usable structure, something like:

ITER   CLASS   VALUE  ROWID
1          A            1           1
1          B            2           1
1          C            9           1
1          A            2            2
...

CLASS   VALUE
A             1
B             3
C             7

With this model you can then simply merge the second dataset onto the first one and do your comuptations.

Re: Using two datasets to compute new values

Ksharp — Mon, 16 Oct 2017 12:23:14 GMT

Here is how to get A_A_P1. But I don't understand your next few questions.

data have;
input A B C Iter;
cards;
1 2 9   1
2 5 7   1
3 9 6   1
4 1 3   2
;
run;
data x;
input A_A B_B C_C;
cards;
1      3      7
;
run;
data want;
 set have;
 if _n_=1 then set x;
 retain A_A_P1;
 if _n_=1 then A_A_P1=A+A_A;
  else A_A_P1=A+A_A_P1;
run;