Contributor
Posts: 31

# Using two datasets to compute new values

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

2

4

7

11

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?

Super User
Posts: 23,353

## Re: Using two datasets to compute new values

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

2. What code do you have so far?

Super User
Posts: 9,441

## Re: Using two datasets to compute new values

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.

Super User
Posts: 10,699

## Re: Using two datasets to compute new values

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;``````
Discussion stats
• 3 replies
• 167 views
• 0 likes
• 4 in conversation