Lag and Lead question

NikosStratis — Wed, 18 Dec 2019 08:28:51 GMT

Hello Team!

I have been stuck on an issue with the LEAD function. I have simple data of a key and a date and i need to create a second date which will be the date of the next record.

i have this data:

data work.have;
infile datalines delimiter=',';
length K2 $26 ;
input K2 $ int_date :date9. ;
datalines;
10947277_0001_01717727444,'24JUL2013'd
10947277_0001_01753176613,'24JUL2014'd
10947277_0001_01783289089,'24JUL2015'd
10947277_0001_01816168072,'24JUL2016'd
10947277_0001_01847358070,'24JUL2017'd
10947277_0001_01847450701,'24JUL2018'd
10947277_0001_01878807619,'24JUL2018'd
10947277_0001_87980687999,'24JUL2019'd
10947277_0002_01717727444,'24JUL2013'd
10947277_0002_01753176613,'24JUL2014'd
10947277_0002_01783289089,'24JUL2015'd
10947277_0002_01816168072,'24JUL2016'd
10947277_0002_01847358070,'24JUL2017'd
10947277_0002_01847450701,'24JUL2018'd
10947277_0002_01878807619,'24JUL2018'd
10947277_0002_87980687999,'24JUL2019'd
10947277_0003_01717727444,'24JUL2013'd
10947277_0003_01753176613,'24JUL2014'd
10947277_0003_01783289089,'24JUL2015'd
10947277_0003_01816168072,'24JUL2016'd
10947277_0003_01847358070,'24JUL2017'd
10947277_0003_01847450701,'24JUL2018'd
;
run;

I need to produce the following data:

Any help is welcomed!

K2	Int_Date	New Date
0947277_0001_01717727444	'24JUL2013'd	'24JUL2014'd
10947277_0001_01753176613	'24JUL2014'd	'24JUL2015'd
10947277_0001_01783289089	'24JUL2015'd	'24JUL2016'd
10947277_0001_01816168072	'24JUL2016'd	'24JUL2017'd
10947277_0001_01847358070	'24JUL2017'd	'24JUL2018'd
10947277_0001_01847450701	'24JUL2018'd	'24JUL2018'd
10947277_0001_01878807619	'24JUL2018'd	'24JUL2019'd
10947277_0001_87980687999	'24JUL2019'd	'24JUL2013'd
10947277_0002_01717727444	'24JUL2013'd	'24JUL2014'd
10947277_0002_01753176613	'24JUL2014'd	'24JUL2015'd
10947277_0002_01783289089	'24JUL2015'd	'24JUL2016'd
10947277_0002_01816168072	'24JUL2016'd	'24JUL2017'd
10947277_0002_01847358070	'24JUL2017'd	'24JUL2018'd
10947277_0002_01847450701	'24JUL2018'd	'24JUL2018'd
10947277_0002_01878807619	'24JUL2018'd	'24JUL2019'd
10947277_0002_87980687999	'24JUL2019'd	'24JUL2013'd
10947277_0003_01717727444	'24JUL2013'd	'24JUL2014'd
10947277_0003_01753176613	'24JUL2014'd	'24JUL2015'd
10947277_0003_01783289089	'24JUL2015'd	'24JUL2016'd
10947277_0003_01816168072	'24JUL2016'd	'24JUL2017'd
10947277_0003_01847358070	'24JUL2017'd	'24JUL2018'd
10947277_0003_01847450701	'24JUL2018'd

Re: Lag and Lead question

PeterClemmensen — Wed, 18 Dec 2019 08:34:34 GMT

Here is one way

data want;
    merge have
          have(firstobs=2 keep=int_date rename=int_date=newdate);
    format int_date newdate date9.;
run;

Re: Lag and Lead question

ed_sas_member — Wed, 18 Dec 2019 08:35:47 GMT

Hi @NikosStratis

As far as I know, SAS does not offer a lead function. There is a much simpler way, through the use of extra SET statements in combination with the FIRSTOBS parameter and other elements of the SET statement:


data want;
	set have;
	if eof1=0 then set have (firstobs=2 keep=int_date rename=(int_date=new_date)) end=eof1;
 	else new_date=.;
run;

Re: Lag and Lead question

NikosStratis — Wed, 18 Dec 2019 08:40:59 GMT

Than you so much!

Re: Lag and Lead question

NikosStratis — Wed, 18 Dec 2019 08:42:00 GMT

Works great! Thank you!

Re: Lag and Lead question

ed_sas_member — Wed, 18 Dec 2019 08:52:16 GMT

You're welcome @NikosStratis
Could you please mark the topic as answered so that it can be made available to the community?
Thank you so much. Have a lovely day.

Re: Lag and Lead question

NikosStratis — Wed, 18 Dec 2019 08:54:54 GMT

I just accepted it! Thanks again!

topic Re: Lag and Lead question in SAS Programming

Lag and Lead question

Re: Lag and Lead question

Re: Lag and Lead question

Re: Lag and Lead question

Re: Lag and Lead question

Re: Lag and Lead question

Re: Lag and Lead question