Lag and Lead question

[NikosStratis]

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

[PeterClemmensen · Accepted solution]

Here is one way

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

[ed_sas_member]

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;

[NikosStratis]

Than you so much!

[NikosStratis]

Works great! Thank you!

[ed_sas_member]

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.

[NikosStratis]

I just accepted it! Thanks again!