topic creating LEAD and LAG together. in SAS Programming

creating LEAD and LAG together.

JKCho — Sun, 28 Jul 2019 00:23:43 GMT

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!!

Re: creating LEAD and LAG together.

data_null__ — Sun, 28 Jul 2019 00:42:51 GMT

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!!

Re: creating LEAD and LAG together.

JKCho — Sun, 28 Jul 2019 02:11:11 GMT

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

Re: creating LEAD and LAG together.

ChrisNZ — Sun, 28 Jul 2019 23:52:09 GMT

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-data-step-yes-you-can/