Generate lead variables in the same data set.

[Golf]

Hello all,   @Kurt_Bremser 

Suppose I have  3 variables  (X, Y, and Z)

    obs    X         Y       Z

      1       10       11       21

      2        2       12      22

      3      40      13      23

      4       4        14      24

       5      80      15      25

Now I want to create one until three periods lead variable of X called X_lead1 X_lead2 X_lead3, while keeping the existing variables as follow.   

obs          X        Y        Z     X_lead1        X_lead2     X_lead3

      1         10       11       21          2                   40              4

      2          2       12      22       40                     4             80

      3       40       13      23         4                   80                .

     4          4        14      24      80                      .                 .

     5        80        15       25        .                        .                 .

Thank You

Could you provide guidance on how to write SAS code to generate the second data set?

[ballardw]

Create one what? This seems like it is missing something: " I want to create one until three periods lead variable of X called X_lead1 X_lead2 X_lead3, "

Define "period" as it is not at all obvious from the data.

Rules for creating the values? You say when, sort of, to create a value, but not how.

---

@Golf wrote:

Hello all,   @Kurt_Bremser 

Suppose I have  3 variables  (X, Y, and Z)

    obs    X         Y       Z

      1       10       11       21

      2        2       12      22

      3      40      13      23

      4       4        14      24

       5      80      15      25

Now I want to create one until three periods lead variable of X called X_lead1 X_lead2 X_lead3, while keeping the existing variables as follow.   

obs          X        Y        Z     X_lead1        X_lead2     X_lead3

      1         10       11       21          2                   40              4

      2          2       12      22       40                     4             80

      3       40       13      23         4                   80                .

     4          4        14      24      80                      .                 .

     5        80        15       25        .                        .                 .

Thank You

Could you provide guidance on how to write SAS code to generate the second data set?

---

[Golf]

"Thank you for your help. Just to clarify, if I change "obs" to "time", would it still make sense? Also, when I say "I want to create one until three period", I actually mean "I want to create data for one to three periods ahead." Lastly, the periods (".") shown in the "want" data indicate that the data is not available." Thank You.

[Tom]

Since it is easier to remember the past than predict the future just sort the dataset in the opposite order and use LAG() functions.

Your example data does not appear to have any variable that can be used for ordering, so let's add one.

data reverse;
  row+1;
  set have;
run;

Now sort and then make the new variables.

proc sort data=reverse;
  by descending row;
run;
data want;
  set reverse ;
  x_lead1 = lag1(x);
  x_lead2 = lag2(x);
  x_lead3 = lag3(x);
run;

[Golf]

Thank You very much for solving my problem. Bests.

[Golf · Accepted solution]

However, when I applied your code as follow:
data reverse;
input time x y z;
cards;
1 10 11 21
2 2 12 22
3 40 13 23
4 4 14 24
5 80 15 25
;
run;

proc sort data=reverse;
by descending time;
run;
data want;
set reverse ;
x_lead1 = lag1(x);
x_lead2 = lag2(x);
x_lead3 = lag3(x);
run;

proc print data = want;
var time x y z x_lead1 x_lead2 x_lead3;
The result is not what I want.

[Golf]

I was able to achieve my goal by reversing the data by time variable once again. Thanks a lot.

[Ksharp]

data have;
input X Y Z;
cards;
10    11     21
2     12   22
40   13    23
4    14    24
80    15    25
;
run;
data want;
merge have 
      have(firstobs=2 keep=x rename=(x=X_lead1))
	  have(firstobs=3 keep=x rename=(x=X_lead2))
	  have(firstobs=4 keep=x rename=(x=X_lead3));
run;

[Golf]

Thank you for sharing the code. I have learned a lot.