Desktop productivity for business analysts and programmers

Using two datasets to compute new values

Reply
Contributor
Posts: 27

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: 20,226

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
Super User
Posts: 8,177

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,123

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;
Ask a Question
Discussion stats
  • 3 replies
  • 152 views
  • 0 likes
  • 4 in conversation