creating LEAD and LAG together.

[JKCho]

proc sort data=mr;
by gvkey fyear;
run;
data mr1;
set mr;
at_1=ifn(gvkey=lag(gvkey) and fyear=lag(fyear)+1,lag(at),.);
run;

proc sort data=mr1;
by descending gvkey fyear;
run;

data mr2;
set mr1;
by gvkey fyear;
at1 = lag(at);
at2 = lag(at1);

Hi all,

this is a very humble code I used for getting LAG and LEAD. Because there is no such lead coding in SAS, I came up with using by descending orders. What I want to obtain is like this.

Let 2015 is the referenced year. If I lag I may have a value of 2014(at_1, meaning total assets of 2015 minus 1, 2014).

by using descending order, I tried to obtain leaded values like at1(total assets of 2015+1).

To me, it looks logically fine but the error code said: ERROR: BY variables are not properly sorted on data set WORK.MR1.

Pretty unsure why it is considered not properly sorted and more wonder how can I obtain lead and lag together..

As I considered lag statement is quite easy to use and clueless now. 

Please share your brilliant wisdom once again.

Thank you so much!!

[data_null__ · Accepted solution]

You need to use DESCENDING in the BY statement in the data step.  

---

@JKCho wrote:

proc sort data=mr;
by gvkey fyear;
run;
data mr1;
set mr;
at_1=ifn(gvkey=lag(gvkey) and fyear=lag(fyear)+1,lag(at),.);
run;

proc sort data=mr1;
by descending gvkey fyear;
run;

data mr2;
set mr1;
by gvkey fyear;
at1 = lag(at);
at2 = lag(at1);

Hi all,

this is a very humble code I used for getting LAG and LEAD. Because there is no such lead coding in SAS, I came up with using by descending orders. What I want to obtain is like this.

Let 2015 is the referenced year. If I lag I may have a value of 2014(at_1, meaning total assets of 2015 minus 1, 2014).

by using descending order, I tried to obtain leaded values like at1(total assets of 2015+1).

To me, it looks logically fine but the error code said: ERROR: BY variables are not properly sorted on data set WORK.MR1.

Pretty unsure why it is considered not properly sorted and more wonder how can I obtain lead and lag together..

As I considered lag statement is quite easy to use and clueless now. 

Please share your brilliant wisdom once again.

Thank you so much!!

---

[JKCho]

oh... no wonder. Thx for pointing out that I haven't seen it myself. What a carelessness..

[ChrisNZ]

This method would be a lot simpler

data T2;                                                 
merge T1 T1(firstobs=2 keep=VAL rename=(VAL=NEXTVAL));     
run;   

See:

https://blogs.sas.com/content/sgf/2015/06/19/can-you-lag-and-lead-at-the-same-time-if-using-the-sas-...