SQL is not suited for look-back/look-ahead issues, the data step is the tool of choice (see Maxim 14). See another approach using mechanisms supplied by the data step:
data want;
set have;
by id amount notsorted;
retain start_date;
if first.amount then start_date = date;
if last.amount
then do;
end_date = date;
output;
end;
keep id start_date end_date amount;
run;
@vaibhavpratap31 wrote: Hi SAS community, Need little help with a code . As LEAD and partition by does not works in PROC SQL . Not able to get the desired output. Have a table like below Id. Date Amount 1. 1-jan. 100 1. 2-jan. 100 1. 3-jan. 500 1. 4-jan. 100 1. 5-jan. 200 1. 6-jan. 200 We need to create a separate table which will have start date and end date based on the change of amount. So the desired output needed is Id. Start Date End Date Amount 1. 1-jan. 2-jan. 100 1. 3-jan. 3-jan. 500 1. 4-jan. 4-jan. 100 1. 5-jan. 6-jan. 200 We only need to use PROC SQL to get the desired result.Thanks for the help in advance. Thanks
... View more
