☑ This topic is solved.
Need further help from the community? Please
sign in and ask a new question.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Posted 01-10-2024 09:41 AM
(1873 views)
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?
1 ACCEPTED SOLUTION
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
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.
8 REPLIES 8
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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_BremserSuppose I have 3 variables (X, Y, and Z)obs X Y Z1 10 11 212 2 12 223 40 13 234 4 14 245 80 15 25Now 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_lead31 10 11 21 2 40 42 2 12 22 40 4 803 40 13 23 4 80 .4 4 14 24 80 . .5 80 15 25 . . .Thank YouCould you provide guidance on how to write SAS code to generate the second data set?
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
"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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Thank You very much for solving my problem. Bests.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
I was able to achieve my goal by reversing the data by time variable once again. Thanks a lot.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Thank you for sharing the code. I have learned a lot.