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 |
Here is one way
data want;
merge have
have(firstobs=2 keep=int_date rename=int_date=newdate);
format int_date newdate date9.;
run;
Here is one way
data want;
merge have
have(firstobs=2 keep=int_date rename=int_date=newdate);
format int_date newdate date9.;
run;
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;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: