topic Re: Generate lead variables in the same data set. in SAS Procedures

Generate lead variables in the same data set.

Golf — Wed, 10 Jan 2024 14:41:36 GMT

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?

Re: Generate lead variables in the same data set.

ballardw — Wed, 10 Jan 2024 16:30:23 GMT

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?

Re: Generate lead variables in the same data set.

Tom — Wed, 10 Jan 2024 16:41:01 GMT

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;

Re: Generate lead variables in the same data set.

Golf — Thu, 11 Jan 2024 01:19:35 GMT

"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.

Re: Generate lead variables in the same data set.

Golf — Thu, 11 Jan 2024 01:24:53 GMT

Thank You very much for solving my problem. Bests.

Re: Generate lead variables in the same data set.

Golf — Thu, 11 Jan 2024 01:33:17 GMT

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.

Re: Generate lead variables in the same data set.

Golf — Thu, 11 Jan 2024 01:40:40 GMT

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

Re: Generate lead variables in the same data set.

Ksharp — Thu, 11 Jan 2024 05:12:50 GMT

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;

Re: Generate lead variables in the same data set.

Golf — Thu, 11 Jan 2024 09:29:50 GMT

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