- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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 |
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Here is one way
data want;
merge have
have(firstobs=2 keep=int_date rename=int_date=newdate);
format int_date newdate date9.;
run;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Here is one way
data want;
merge have
have(firstobs=2 keep=int_date rename=int_date=newdate);
format int_date newdate date9.;
run;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content