how to calculate the value at row x + 1 based on the value at row x

[alepage]

Hello ,

I would like to build a table like that

date      noexec

sept 11     11

                 12

                  13

                  14

                   15

                   16

sept 12       21

                   22

                   23

                   24

                   25

                   26

....

jan 31 2022 ....

[Reeza]

What is the starting point? What is the input?

[alepage]

The starting point is september 11, 2021 and for the first row the value will be 0011, then second row 0012, third row, 0013, forth row , 0014, fifth row, 0015, sixth row, 0016 Then for the next day, we add 10 to each line so, 7th, 0021, 8th, 0022, 9th, 0023, 10th, 0024, 11th, 0025, 12 th, 0026

and so on.

[Reeza]

Please don't make me guess. Can you show an example of your input data set and output data set.

[alepage]

11-sept-21	9976	0011	0011
11-sept-21	0011	0012	0011
11-sept-21	0012	0013	0011
11-sept-21	0013	0014	0011
11-sept-21	0014	0015	0011
11-sept-21	0015	0016	0011
12-sept-21	0016	0021	0021
12-sept-21	0021	0022	0021
12-sept-21	0022	0023	0021
12-sept-21	0023	0024	0021
12-sept-21	0024	0025	0021
12-sept-21	0025	0026	0021
13-sept-21	0026	0031	0031
…	…	…	…
14-sept-21	0036	0041	0041
…	…	…	…
15-sept-21	0046	0051	0051
…	…	…	…

[Reeza]

This is your input to become the initial post?
I'm sorry I don't understand your problem 😞

[alepage]

The example below is the dataset I would like to obtain.

At the first row, the value of var1 is given or imposed.

The alway for var1, starting at the second row, it value is based on the previous observation of var2.

Var3 take the first value of var2 for six rows, then take the value of var2, _n_=7 and that for 5 subsequent rows.

var2 start with 11 and at each row, for six iteration, we add one .  The at _n_=7 we add 10 to the value an _n_=1 , and we add one to next value and so on.

So, imagine that we have the first row and we want to build the complete table as the example 

[Tom · Accepted solution]

---

@alepage wrote:

The starting point is september 11, 2021 and for the first row the value will be 0011, then second row 0012, third row, 0013, forth row , 0014, fifth row, 0015, sixth row, 0016 Then for the next day, we add 10 to each line so, 7th, 0021, 8th, 0022, 9th, 0023, 10th, 0024, 11th, 0025, 12 th, 0026

and so on.

---

So you want to start with :

data have ;
  input date :date. x;
  format date date9.;
cards;
11SEP2021 11
;

And end up with 858  observations?

data want;
  set have;
  offset1=0;
  do while (date <= '31JAN2022'd);
    do offset2=0 to 5 ;
      want=x+offset1+offset2;
      output;
    end;
    date + 1;
    offset1 +10;
  end;
run;

First 20 observations:

Obs         date     x    offset1    offset2    want

  1    11SEP2021    11        0         0        11
  2    11SEP2021    11        0         1        12
  3    11SEP2021    11        0         2        13
  4    11SEP2021    11        0         3        14
  5    11SEP2021    11        0         4        15
  6    11SEP2021    11        0         5        16
  7    12SEP2021    11       10         0        21
  8    12SEP2021    11       10         1        22
  9    12SEP2021    11       10         2        23
 10    12SEP2021    11       10         3        24
 11    12SEP2021    11       10         4        25
 12    12SEP2021    11       10         5        26
 13    13SEP2021    11       20         0        31
 14    13SEP2021    11       20         1        32
 15    13SEP2021    11       20         2        33
 16    13SEP2021    11       20         3        34
 17    13SEP2021    11       20         4        35
 18    13SEP2021    11       20         5        36
 19    14SEP2021    11       30         0        41
 20    14SEP2021    11       30         1        42
...

[ballardw]

---

@alepage wrote:

Hello ,

I would like to build a table like that

date      noexec

sept 11     11

                 12

                  13

                  14

                   15

                   16

sept 12       21

                   22

                   23

                   24

                   25

                   26

....

jan 31 2022 ....

---

You really need to provide an example of the output for that Jan 31 2022 value.

And why does Sep 12 start at 21 when Sep 11 starts at 11? Is that just your actual data?

Which value is X? Where does the X+1 go? Into the same or a new variable?

Does the actual value of the "date" every have an effect on the value calculation or is it just a "group" indicator that says we have to start over on the first value?

I am guessing a whole lot of things here that part of the example below applies to your data.

Note that I have built a data set with a grouping variable, that I think is used in the role of your "date" and a value of X.

If your data doesn't look like that, then modify the data step and show us YOUR data.

Data have;
   input group $ x;
datalines;
A  11
A  11
A  11
A  11
A  11
A  11
B  26
B  26
B  26
B  26
B  26
B  26
B  26
B  26
B  26
B  26
;

data want;
   set have;
   by notsorted group;
   retain newx;
   if first.group then newx=x;
   else newx+1;
run;

This works, if related to your problem, because the BY statement allows testing for whether a variable is the First (or Last) of group of values. I use the not sorted because I am not sure from your "examples" whether your data is sorted or not which is normally the requirement to use BY. The First and Last values, referenced with a dot between the key word and the name of the variable, are numeric values of 1 and 0 with 1 treated as true and 0 as false. So we can set something when the first value of the group variable is encountered.

RETAIN means that the value of the variable is kept across iterations of a data step. So set one value the first time and then add 1 on following iterations of the values in that group.

If the actual date values have some impact on how/what is calculated then your first thing may be to make sure the values are actually SAS date values, i.e. numeric values with a date format applied (for people to read).

ALL of your examples so far have a serious problem in that your question asks about X and X+1. But there is not indication which variable is X.